How to restore database with 2 mdf and 2 ldf files leaving only 1 mdf/ldf pair?

Question

I've got a .bak file (SQL Server 2008) which contains 2 mdf and 2 ldf files.

When I try to restore a database from it, it creates all 4 of those files.

I've never seen this before, and was wondering how do I go to the good-old 1 file pair system (1 mdf/1 ldf)?

Any insight on 2 file pairs being in the backup will help as well.

p.s.: my setup does not have any Ndf files, both are Mdf

Answer 1

It sounds like you'll need to move the data from the secondary mdf(isn't it ndf?) into the first. This all assumes you've done the restore.

Let's say your 2 files are m1 and m2.

Use DBCC SHRINKFILE to move and empty your ndf.

--move all data out of this file
DBCC SHRINKFILE ('m2', EMPTYFILE )

You likely will have to restart SQL Server in single user mode. (source)

--if trying to delete LDF file, do a Transaction log backup of your DB now

--remove this file from the 
ALTER DATABASE DB_NAME_HERE_NO_QUOTES REMOVE FILE m2