Sql-server – SQL Server – SQL script to restore database backup file into a new database

restoresql server

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:

RESTORE DATABASE new_db
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:

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

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

Best Answer

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.

Related Question