Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

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

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

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
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
    
everything works now, thanks! –  Li. May 30 '11 at 10:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.