Restoring a Multi-File Database

Recently I was trying to restore a standard SQL Server database from .bak file and this error came up:

Here are the steps I took to overcome the error

1. If one doesn’t already exist, create a new empty database with the desired name.  You may not actually have to do this, but this is what I did to get it work.  If works for you without creating the empty database let me know in the comments.

2. In the restore window choose SCRIPT from the menu at the top of the dialog.  From the drop down pick “Script Action to New Query Window”

 

3. The resulting query will look something like this

RESTORE DATABASE [DBName]
FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.bak'
WITH FILE = 1,
MOVE N'ABC_database_123'
TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.mdf',
MOVE N'DEF_database_123'
TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.mdf',
MOVE N'ABC_database_123_Log'
TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO

4. Don’t run the query yet.  If you do you’ll likely get an error that you can’t access the second file.  This is because you can’t combine two MDF files.  Change the extension of the second and subsequent data files to NDF.  NDF files are used by SQL Server to spread databases across disks and servers allowing for larger databases.  But that’s a topic for another time.  Leave the LDF file as LDF.  The resulting query looks like this

RESTORE DATABASE [DBName]
FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.bak'
WITH FILE = 1,
MOVE N'ABC_database_123'
TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.mdf',
MOVE N'DEF_database_123'
TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.ndf',
MOVE N'ABC_database_123_Log'
TO N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDataDBName.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO

5. Run the query.  After a while the query results will show the percentage complete and you should get a notification for success, or failure.

6. The only thing left is to create a user, or fix orphaned users if there are any.  I have an article here on how to fix orphaned users.

Now go and restore a database!