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

I was trying to restore database using T-SQL and I ended up with error message “Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing ‘GlobalObjects’ database.”. Sometimes I like errors but It depends on how busy I am. But It was saturday so I liked it to google and solve it.

I tried to restore the database using SQL Server Management Studio. But when I select .bak file to restore, I didn’t see anything under “Select the backup sets to restore”. Strange for me… Then I thought let’s restore using T-SQL so atleast it will give me some error message what’s going on. I tried following T-SQL to restore database.

RESTORE DATABASE Test
FROM DISK = N’C:UsersshaileshDesktopgoTest_CopyBackup.BAK’
WITH MOVE ‘Test’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataTest.mdf’,
MOVE ‘Test_Log’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataTest_log.ldf’

When I ran this T-SQL I got the error what I talked about earlier.

Then I ran following T-SQL to look into more detail about .bak file.

RESTORE FILELISTONLY FROM DISK = ‘C:UsersshaileshDesktopgoTest_CopyBackup.BAK’

Look at the first column “LogicalName” magic is here. Now go back to database you have created to restore the database and verify the LogicalName. By default SQL Server name it like “DatabaseName” (here in our case Test) for file type “Data” and “DatabaseName_Log” for file type “Log”. So It is quite possible that database backup file you are trying to restore has a different logicalname than what you have for the database. So we need to change restore T-SQL little bit to successfully restore database or you have to change name using Management studio if you don’t use T-SQL to restore database.

Here is my modified T-SQL to restore database.

RESTORE DATABASE Test
FROM DISK = N’C:UsersshaileshDesktopgoTest_CopyBackup.BAK’
WITH MOVE ‘PrimaryFileName’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataTest.mdf’,
MOVE ‘PrimaryLogFileName’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataTest_log.ldf’

I ran this T-SQL and boommmmmmmmm. I am done !!!!

Hope this helps you !!!!

Posted in Microsoft Technology Tagged with:
5 comments on “The backup set holds a backup of a database other than the existing database
  1. Ben says:

    Thank you so much

  2. Anonymous says:

    you sir are a genius.
    saved me some time. thanks

  3. Anonymous says:

    Wonderful. it worked perfectly without any glitches

  4. Anonymous says:

    This was right in the mark thanks

  5. Shriti says:

    its giving me error.
    i am going to restore ‘restoreDB’ database with ‘Attendance2010’ database backup path. my query is
    ALTER procedure [dbo].[_backup_RestoreDB] ‘E:Attendance’,0
    @path as varchar(1000),
    @isBackup as bit
    as
    if(@isBackup=1)
    BACKUP DATABASE Attendance2010 TO DISK = @path
    else
    RESTORE DATABASE restoreDB
    FROM DISK = @path
    WITH MOVE ‘Attendance2010’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATArestoreDb.mdf’,
    MOVE ‘Attendance2010_log’ TO ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLDATArestoreDb_log.ldf’

    Error message:
    Msg 3154, Level 16, State 4, Procedure _backup_RestoreDB, Line 8
    The backup set holds a backup of a database other than the existing ‘restoreDB’ database.
    Msg 3013, Level 16, State 1, Procedure _backup_RestoreDB, Line 8
    RESTORE DATABASE is terminating abnormally.

Ads