Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Retore SQL Server database to different filenames and locations



By:

Overview

The RESTORE ... WITH MOVE option allows you to restore your database, but also specify the new location for the database files (mdf and ldf).  If you are restoring an existing database from a backup of that database then this is not required, but if you are restoring a database from a different instance with different file locations then you may need to use this option.

The RESTORE ... WITH MOVE option will let you determine what to name the database files and also what location these files will be created in.  Before using this option you need to know the logical names for these files as well as know where SQL Server will restore the files if you do not use the WITH MOVE option.

If another database already exists that uses the same file names you are trying to restore and the database is online the restore will fail.  But if the database is not online for some reason and the files are not open, the restore will overwrite these files if you do not use the WITH MOVE option, so be careful you do not accidently overwrite good database files.

Also, when using the WITH MOVE option you need to make sure the account used for the SQL Server engine has permissions to create these files in the folder you specify.


T-SQL

Determine contents of backup

So the first thing you need to do is determine the logical names and the physical location of the files.  This can be done by using the RESTORE FILELISTONLY command.  This will give you the logical and physical names.

Here is an example:

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

This gives us these results:

ColumnName Value - Row 1 Value - Row2
LogicalName AdventureWorks_Data AdventureWorks_Log
PhysicalName C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf
Type D L

Restore full backup WITH MOVE
So let's say we want to restore this database, but we want to put the data file in the "G:\SQLData" folder and the transaction log file in the "H:\SQLLog" folder.  The command would be like the following:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLLog\AdventureWorks_Log.ldf'
GO

Restore full and transaction log backup WITH MOVE
The WITH MOVE only needs to be specified for the first restore, because after this the database will be in a "restoring" state.  The second restore will just write the contents to this new location that is being used.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLLog\AdventureWorks_Log.ldf',
NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

SQL Server Management Studio

To restore using SSMS do the following, on the options page for the restore, change the "Restore As:" values for each file as shown below.  The below restores to the root folders, but you could change these to go to G:\SQLData\ and H:\SQLLog\ as needed.


Last Update: 3/19/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 31, 2017 - 10:09:35 AM - Greg Robidoux Back To Top

Thanks rsreynolds. This has been fixed.

-Greg


Tuesday, October 31, 2017 - 9:07:15 AM - rsreynolds Back To Top

 

 ditto for H:\SQLLog & H:\SQLData.


Thursday, September 29, 2016 - 9:44:02 AM - Greg Robidoux Back To Top

Thanks Patrick for pointing out the typos.  I updated the code to reflect the proper naming conventions and also add a note to the SSMS GUI restore where I did not specify the SQLData folders.

-Greg


Wednesday, September 28, 2016 - 5:58:28 PM - Patrick McVey Back To Top

I have certainly btoched up the syntax of a command or two then spent much time critically gazing upon the typography. Unfortunately, it has caused me to scour every command I see, like yours. You have a typo in here. First, the database is named AdventureWorks but the file location gets Advnet-something on both data & logs. Second, you state you want the logs to move to H:\SQLLogs folder. But you disregard the instruction and move it to H: drive but the SQLData folder.

Don't feel bad, the take-away is learning how the command works. And this helped. Thanks!

    


Learn more about SQL Server tools