Multiple File Restore

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.

Screen Shot 2016-06-07 at 7.45.38 AM


Now we back that database up.

Screen Shot 2016-06-07 at 8.08.59 AM

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

Screen Shot 2016-06-07 at 8.10.48 AM

We will want to restore from a Device.

Screen Shot 2016-06-07 at 8.16.21 AM

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

Screen Shot 2016-06-07 at 8.17.45 AM

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.

Screen Shot 2016-06-07 at 8.28.51 AM

Screen Shot 2016-06-07 at 8.29.54 AM

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

Screen Shot 2016-06-07 at 8.30.41 AM

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

Screen Shot 2016-06-07 at 8.33.05 AM

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:





So The whole thing would look like this.

Screen Shot 2016-06-07 at 10.13.38 AM


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.




SQL Help

%d bloggers like this: