Sanganak Authority: The backup set holds a backup of a database other than the existing database

Monday, January 30, 2012

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

Yesterday I received a database backup from my client and he told me to restore it to current database. I asked him how much time I have. Being from non-technical background, he said you 1 full day to do this and I was so happy. I was quite sure that, restoring database backup is of 5 minutes job using SQL Server Management Studio.

So needless to stay, whole day I was in table tennis room. It was only half an hour remaining for end of day, I came to desk and tried to restore the backup and I received following error –

TITLE: Microsoft SQL Server Management Studio

------------------------------

Restore failed for Server 'MySQLServerName. (Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'MyDBName' database. (Microsoft.SqlServer.Smo)

Error Id -3154

I was trying to restore my backup from SSMS only and I received this error every time I tried to restore.bak file.

Solution –There are two ways by which you can restore .bak file in SQL Server 2008

Using SQL Query –

We need to use “WITH REPLACE” in RESTORE command. SQL command is as follows –

RESTORE DATABASE MyDatabaseName

FROM DISK = 'C:\kunal\Learning Applications\MyDB.bak'

WITH REPLACE

It is not necessary to create empty database with name specified, before restore operation. This is basically overwriting of existing file and works best.

Using SQL Server Management Studio –

In SSMS, you need to create an empty database first. Right click on “Databases” on the root [not newly created database] and choose “Restore Database…” option [not “files and filegroups” option] as shown below.

Then select .bak file from physical location in the pop up which appears when you click on button present next to label “From Device” textbox. Then click OK. The selected file gets added in the lower pane. Make sure that, you select the checkbox against file name added in the lower pane. Then click on Option in the right hand side of the window.

The select the checkbox labeled as “Overwrite the existing database (WITH REPLACE)” option. If you see in screenshot there are 4 checkbox options available. If only 3 checkbox options available means you have selected “Restore files and filegroups” option and not “Restore Database…” option. So please be careful.