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.
Explanation
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.
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'
GOThis 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'
GORestore 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'
GOSQL 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.
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
