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?
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.