Remove (merge) SQL Servers' database secondary data file

Question

I have a database backup for which SQL Server Management Studio says that it has three files in it: an `.mdf` file, an `.ndf` file and one `.ldf` file. This secondary data file (the `.ndf` one) was created for no obvious reason, so I want to remove it altogether (without losing data, of course), preferably during while the database is being restored from the backup. Is this at all doable?

Accepted Answer

Ok, found a solution.

First back up the database.

Execute this:

```
USE database_name;
```

Then execute this, and replace `logical_ndf_file_name` with the logical name of your NDF file (which you can easily find out via Database->Properties_Files):

```
DBCC SHRINKFILE('logical_ndf_file_name', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logical_ndf_file_name;
```

Remove (merge) SQL Servers' database secondary data file

Solution

Problem