2

I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only. The name of the database on the new location should not be identical to the original name.

I've tried the following commands, but failed when I tried to restore the database:

Backup:

sqlcmd -E -S MyUser\SQLEXPRESS -Q "BACKUP DATABASE original_db TO DISK='C:\tmp\backups\base_db.bak'"

Restore:

sqlcmd -E -S MyUser\SQLEXPRESS -Q "RESTORE DATABASE new_test_db FROM DISK='C:\tmp\backups\base_db.bak' 
with move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.mdf', 
    move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.ldf'"

When I try to restore the database I get the following error: "The backup set holds a backup of a database other than the existing 'new_test_db' database."

(When my database didn't exist I got the following error on restore - "Logical file 'original_db' is not part of database 'new_test_db'. Use RESTORE FILELISTONLY to list the logical file names.")

Any ideas how to do it properly?

Thanks for the help.

migrated from stackoverflow.com Jan 21 '12 at 15:00

This question came from our site for professional and enthusiast programmers.

  • the restore script: sqlcmd -E -S MyUser\SQLEXPRESS -Q "RESTORE DATABASE new_test_db FROM DISK='C:\tmp\backups\base_db.bak' with replace, move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.mdf', move 'original_db_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.ldf'" – Li. May 30 '11 at 10:51
7

The WITH MOVE 'logical_file_name_in_backup' refers to the internal name of that file, not the database name.

Run this to see what I mean. You're looking at the name column

SELECT * FROM sys.database_files

Edit:

Need WITH REPLACE now... see first link above and look for

The database name is different from the database name recorded in the backup set.

  • thanks, my database didn't exist, so that's why I got the previous error, I've edited my question. Now the actual problem appears - "The backup set holds a backup of a database other than the existing 'new_test_db' database." – Li. May 30 '11 at 9:13
  • thanks, this is exactly what I was looking for, though now after using both replace and move (+ tried without the move) I'm getting the first error again regarding the file names...any ideas what the full command should look like? (I'm currently trying to restore the db on the same sqlserver with a different name) – Li. May 30 '11 at 9:59
  • @Li: Did you change ...with move 'original_db' to... to ...with move '<insert name from sys.database_files>' to...? – gbn May 30 '11 at 10:01

Your Answer

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