Sql-server – How to backup and restore a database to a different location and name via command line only

sql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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.

Related Question