restore .bak mssql 2005 to a new a server of mssql 2008r2

3
votes

I created a .bak file (backup) from server A with the with following specs: Windows server 2003, MSSQL 2005. Now I would like to restore this backup on a new Server B with the following specs: Windows 8, MSSQL 2008 R2. I did the following to try and do the restore

  1. Copy files to the new server(Server B)
  2. Clicked on Microsoft SQL Server Management Studio 2008R2(Server B)
  3. Right click on Databases to create a new Database called Boom (Server B)
  4. After creating the new Database(Boom), right clicked on Tasks->Restore->Database and
  5. On the source for restore area, Clicked From device and located the .bak file, select it and cliked ok.

Instead of getting the success message, I get the following error:

Restore failed for Server 'Server B'.(Microsft.SqlServer.SmoExtended) Additional information: System.Data.SqlError: The backup set holds a backup of a database other than the existing 'Boom' database(Microsoft.SqlServer.Smo).

Please assist, Im am new to MSSQL

2
sql-serverdatabasesql-server-2008sql-server-2005backup
Right click on Databases to create a new Database called Boom (Server B) This is not necessary. – ta.speot.is

2 Answers

4
votes

Right click on Databases to create a new Database called Boom (Server B)

After creating the new Database(Boom), right clicked on Tasks->Restore->Database and

Well now you're making a new database and trying to overwrite it with a different database's backup. Hence:

The backup set holds a backup of a database other than the existing 'Boom' database

There's a WITH REPLACE option that allows you to proceed, but just avoid the indirection to begin with: restore the database, don't make a new one.

enter image description here

You'll probably need to delete the redundant Boom database you made, first. If, for whatever reason, you couldn't delete the database you'd have to use WITH REPLACE.

enter image description here

0
votes

"Restore failed for Server 'Server B'.(Microsft.SqlServer.SmoExtended) Additional information: System.Data.SqlError: The backup set holds a backup of a database other than the existing 'Boom' database(Microsoft.SqlServer.Smo)."

I encountered this error when the logical name of the files do not match. Check on the logical name of the database you backed up and you will need the same logical name for the new database you created.

Or you can surely use the With Replace option too as specified by ta