I’ve come across multiple situations where I have to do a restore of a database that not only has multiple data files, but the data files are in different locations.  If I’m doing a restore from a production system into Dev the file locations in Dev are almost 100% guaranteed to be different than production.  As much as I love typing out line after line after line of code, and hoping I don’t have one little syntax error, I figure there has to be a better way….and there is.  Here comes SSMS (SQL Server management Studio) to the rescue.  We are going to use SSMS to generate all the code for me so I can easily do my restore, and so I can save the script for future use.

First we need to setup a database with multiple files.  I used AdventureWorks2014 and added a new file in SSMS.  Note, the test database has all of it’s data files in the same place but that won’t matter for our little test.

 

Now we back that database up.

And now we use the power of SSMS by right clicking on the database and picking Tasks–>Restore–>Database.

We will want to restore from a Device.

Press the … button on line that says Device, and navigate to the backup file and press OK twice.

Set your location to move your files to and make sure Overwrite is selected.  I’m moving all the data files to the same location for this test, if you needed to put them into different locations just uncheck ‘Relocate all files to folder’ and set the path for each data file.

Now press the drop-down arrow next to the word Script.

SSMS lets you save all this work into multiple options, I’ll use the first one for this.

So the question is “Why not just do the restore from SSMS?  Why create this script?”  In my experience you are going to want to wrap your restore in a script that puts the database into single user mode, does the restore, and then puts the database back into multi-user mode.  If you rely on SSMS you will run into blocking issues and get very frustrated very quickly.

Here is what I use:

ALTER DATABASE [DB_NAME]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE SCRIPT GOES HERE

ALTER DATABASE [DB_NAME] SET MULTI_USER
GO

 

So The whole thing would look like this.

 

You can save this script for future requests, especially if you get a lot like I do, and alter it as needed for different databases.