When restoring a database from an SQL backup .bak, one usually creates a database and then selects the restore function. As soon as you try to restore the database you get the error saying “
The backup set holds a backup of a database other than the existing“. This is because it fails to read the files from the restore and matching them to the newly created files.
The error says:
Restore failed for Server ‘SQLSRV01’. (Microsoft.SqlServer.SmoExtended)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The backup set hold a backup of a database other an the existing ‘test_restore’ database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
To solve this one should do the following:
– Don’t create an empty database and restore the .bak file on to it.
– Use ‘Restore Database’ option accessible by right clicking the “Databases” branch of the SQL Server Management Studio and provide the database name while providing the source to restore.
This should allow you to restore the database with no error and fix the error 3154.
When running an integrity check on your databases, you may get the following error on some databases.
Description: Executing the query "DBCC CHECKDB(N'mydb') WITH NO_INFOMSGS " failed with the following error: "The In-row data myrow page count for object "my_table", index ID 0, partition ID 52794353778688, alloc unit ID 52794353778688 (type In-row data) is incorrect.
This is due to the fact that the database in question was imported from another or older version of your current SQL server. This can be fixed by running the below command on the databases in question.
DBCC UPDATEUSAGE (dbname)
dbname = The name of your database
When the database is indeed one which is failing the integrity check you will see the below sample message.
DBCC UPDATEUSAGE: Usage counts updated for table 'mytable' (index 'mytable', partition 1):
DATA pages (In-row Data): changed from (2) to (1) pages.
USED pages (In-row Data): changed from (25) to (24) pages.
RSVD pages (In-row Data): changed from (41) to (40) pages
If the database doesn’t need to update it’s usage you will simply get the message below.
DBCC execution completed.
Once you have executed the update-usage on all databases which are failing, run the integrity check again and swish, it works.