Summary: You can encounter "The backup set holds a backup of a database other than the existing database" error while restoring the database from the .BAK file in SQL. It occurs if the backup set holds an incompatible version backup. In this post, we’ll discuss the causes behind this error and how to fix it. If the .BAK file restore fails, you can use MS SQL repair tool, mentioned in this post, to recover data from the backup file.
Several users have reported encountering an error when restoring the backup (.BAK) file in SQL Server. The complete error message reads as, “The backup set holds a backup of a database other than the existing ‘xxx’ database. RESTORE DATABASE is terminating abnormally (Microsoft SQL Server error: 3154).
Here are some of the user queries:
Query 1:
I am trying to restore a SQL Server backup file for my database, but it is throwing an error as follows:
The backup set holds a backup of a database other than the existing.
My database is in SQL Server 2008 and the backup file is in 2005.
Query 2:
I’m trying to restore my production .bak file to a new local instance, but I am getting this error:
The backup set holds a database backup other than the existing MyDatabase database.
I’m using SQL Server 2019.
Query 3:
I got the below error when I tried to restore a database from Azure Blob Storage to my local SQL Server using the SQL Server Management Studio (SSMS).
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘{DB_Name}’ database. (Microsoft.SqlServer.SmoExtended)What causes the Error 3154: “The backup set holds a backup of a database other than the existing database?”
The error indicates that there is an issue with the backup or original SQL database file. It might also happen that you’re using incorrect file path in the “RESTORE DATABASE” command. Below are some other reasons that can lead to this error in SQL Server:
- The .BAK file is damaged.
- You are using incorrect query to restore the .BAK file.
- The original database file is corrupted.
- Incompatible file version.
- You do not have required permissions on the folder where your backup file is located.
- The database you are trying to restore is already in use.
Solutions to Resolve SQL Error 3154: The backup set holds a backup of a database other than the existing database
This error can occur if there are integrity issues in the backup set. So, use the RESTORE statement WITH VERIFYONLY option to verify if the backup set is complete and the backup is readable. When you execute this command, it will generate message indicating the issues with the backup set (if any). Based on the message, you can troubleshoot the issue. If SQL Server Database Engine returns a success message, this means your backup file is fine. Then, you can follow the below solutions to fix the error:
Solution 1 – Close Existing Connections
The SQL database restore error 3154 can also occur if there are active connections to the database. Ensure all the active connections between the database and the Management Studio are closed. To check and close existing connections, follow these steps:
- Open SSMS, right-click on the databases and select Restore database.
- Under the Restore Database window, select Options.
- Under Server connections, select “Close existing connections to the destination database.” Click OK.
Note: When you select this option, it sets the database to single-user mode before the restore operation and sets it to multi-user mode when the restore operation is complete.
Solution 2 – Restore Backup with WITH REPLACE Option
If you are facing the “The backup set holds a backup of a database other than the existing one” error while executing the RESTORE DATABASE command in T-SQL, you can add “WITH REPLACE” option to the command. It helps in removing the incorrect backup set (if any) by overwriting the existing database.
Here is the syntax:
RESTORE DATABASE testing
FROM DISK = 'C:\Backuptesting.bak'
WITH REPLACEImportant:
- Restoring the SQL database “WITH REPLACE” option can overwrite any database that is in the backup set, even if the specified database name not matches with the database name stored in the backup set.
- After executing this, you will not be able to undo the changes.
- Before executing this command, make sure you have the desired permissions to restore the database from the .BAK file.
- Ensure that you can easily access the specified backup file path in the command.
- Make sure that the database you are trying to restore is in a correct restoration state. To check this, refer Microsoft’s Restore Database options page.
Alternatively, you can use the SSMS to restore backup with “WITH REPLACE” option if you don’t want to execute queries. Here are the steps:
- In Microsoft SQL Server Management Studio (SSMS), right-click on “Databases” and select “Restore Database.”
- Select the source database. Click the Browse option to open the backup devices dialog box.
- Click Add and go to your backup.
- Once you selected backup file, then click OK.
- Next, click OK to go back to the General window.
- In the General window, click Options. Under Restore Options, select the following:
- Overwrite the existing database (WITH REPLACE).
- Preserve the replication settings (WITH KEEP_REPLICATION).
- Click OK.
What If Nothing Works?
If the above methods failed to fix the error “The backup set holds a backup of a database other than the existing database”, then it indicates your backup file is corrupt. In such a case, you can use a third-party SQL database repair software, such as Stellar Repair for MS SQL Technician. This tool comes with a dedicated backup recovery utility that helps extract data from corrupted SQL Server backups – be it Full, Differential, or Transactional. It can help to restore all the objects from the corrupted SQL backup (.BAK) files with complete integrity. It offers options to save the recovered objects in a new database or an existing database. It also allows to save the data in other file formats, such as XLS, CSV, and HTML. The tool supports SQL Server 2022, 2019, and earlier versions.
Conclusion
The MS SQL Backup restore error “The backup set holds a backup of a database other than the existing database” can occur while restoring the backup file. Make sure you are using a valid backup file. To prevent the error, you can restore the database using the ‘RESTORE’ command with the ‘Replace’ option or check and close the existing connections. If the database backup you are trying to restore is corrupt, then you can use Stellar Repair for MS SQL Technician. It can extract all the data from the corrupted SQL backup file. It can help resolve the backup set issues if they occur due to consistency issues within the .BAK files. The tool is compatible with all the versions of SQL Server, including 2022 and 2019.
