0

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.

  • 2
    The source of the MOVE is the logical name of the database file in question, not it's old physical name. You can get the logical names by doing FILELISTONLY restore, or querying the existing database sys.database_files. – Jonathan Fite May 29 at 12:29
  • 1
    Also, after going through the restore wizard, you can generate the SQL commands it would use by hitting the "Script Command" button near the top of the window. – Jonathan Fite May 29 at 12:30
  • That was the problem, yes. I have now changed it and it works. Thanks! – Pitrako Junior May 29 at 13:10
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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