Getting SqlServer2005 error: backup set holds a backup of a database other than the existing 'DbName' database

Question

I am trying to upgrade from a server running SqlServer2005 (no service packs) to a server running SqlServer2005 service pack 3.

I am trying to copy the databases from one to another by doing a backup on one, then a restore on the other.

I am using ssms.

When I try to restore a backup of, call it DbName, created on the first server, onto the second server (I created a db DbName on the second server) I get the error message:

backup set holds a backup of a database other than the existing 'DbName' database

What's going on here and how do I fix it?

Answer 1

If you created a database named DbName and you are trying to apply a backup of a different database (also named DbName) from the first server, then you will get the error that you are trying to back up a different database. This prevents mistakes where you would accidentally replace a database with the the wrong backup content.

There are two solutions, both trivial:

• use the WITH REPLACE option of the RESTORE command, so that it replaces the database content with the different database backup set:

  Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted.

• explicitly drop the existing database before the RESTORE operation.

SSMS has a special wizard that does that: right click on database, Tasks/Copy Database... but I much rather do it manually with straight T-SQL commands.