I am trying to create a SQL script to restore a database backup file into a new database in SQL Server.

I can do it manually without problems:

  • Right click on Databases => Restore Database
  • Tick on Device
  • [General] Browse to the location where the BAK files is located and add it (click ok) Destination => Database => Type in name of new database
  • [Files] Change the names of the mdf and ldf files in 'Restore As'
  • [Options] Un-tick Tail-Log backup

In order to do this with a SQL script I am trying with the following:

FROM DISK = 'E:\test\test.BAK' WITH
MOVE 'E:\MSSQLSERVER\DATA\old_db.mdf' TO 'E:\test\new_db.mdf',
MOVE 'E:\MSSQLSERVER\LOG\old_db_log.ldf' TO 'E:\test\new_db.ldf'

I get the following error:

Logical file 'E:\MSSQLSERVER\DATA\old_db.mdf' is not part of database 'new_db'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

If I run this:

FROM DISK = 'E:\test\test.BAK'

...and then I run the RESTORE script above, I get the same error.


1 Answer 1


Putting here as the comment I made did solve the problem.

The source of the MOVE syntax is the logical name of the file, not it's old physical name. You can get the logical name by doing a FILELISTONLY restore or by querying sys.database_files if you still have access to the source.

MOVE <logical name> TO <path and name of file to use>

Additionally, most (all?) of the various wizards through Management Studio have an option to script the commands out for you. This is invaluable for learning the syntax and various options.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.