This is a follow up of the blog post I have posted on error 3154 few years ago. I have received quite a few emails that how we can fix the same error with the help of SQL Server Management Studio. In this blog post, I will explain how we can do the same with SSMS. Here is the error code:
Error 3154: The backup set holds a backup of a database other than the existing database.
You can fix that with the help of T-SQL with the following command.
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
If you want to do the same with SSMS, you can follow the steps here:
Step 1: Create a database with your preferred name. (In our case AdventureWorks)
Step 2: Write click on the database and click on Tasks >> Restore >> Database…
Step 3: On the restore screen go to third selection of Options. Now select the checkbox “Overwrite the existing database (WITH REPLACE)”
Step 4: Click OK. It should successfully restore the database.
Note: When you restore a database WITH REPLACE it will overwrite the old database.
Relevant Blog Post:
FIX : Error 3154: The backup set holds a backup of a database other than the existing database
Reference : Pinal Dave (https://blog.sqlauthority.com)
Still error occur even clicking the “Overwrite the existing database with replace.”
Very well explained! TSQL worked. Thank you.
Thanks. SSMS options too worked.
Hello i tried above solution But i am getting error message
Msg 3634, Level 16, State 1, Line 2
The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\TEST.mdf’.
Can you please give me any solution for this.
You likely need to go into that directory to that file and change the permissions to allow read/write for everyone, or at least the user you are trying to do the restore from.
I am using SQL server 2014 Enterprise edition. When I am trying to restore SQL 2008R2 backup file I getting ‘No backupset selected to be restored.’ error. So could you please suggest a solution for to solve this issue.
My pleasure @Ramesh
THANK YOU , it is very helpful
I am glad that it was helpful Master Can.
thank for this article,,very helpful
secret – Thanks for letting me know. Appreciate it.
Thanks for helping me
Rahul – thanks for letting us know.
Thanks Mr. Dave Love You…
Thank you, it worked.
HI, I am trying to a move/migrate a sql Server 2000 database from an old server(Windows server 2003) to sql server 2014 on to new server(Windows server 2012R2). As I read in google that we can not restore it directly I followed the below steps.
1. Back up database on sql 2000
2. Restore it on 2008, change the compatibility level to 100
3. Take a back up on 2008 again
4. Restore it on 2014
When I try to restore it on 2014, getting the below error.
No backupset selected to be restored.
Please let me know what are the steps to be followed.
how can i restore back up file with .file extention
Dear sir RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\BackupAdventureworks.bak’
WITH REPLACE not work actually i need restore db by sql query
I have already used
ALTER DATABASE db_Demo
WITH ROLLBACK AFTER 30
— RESTORE Database
RESTORE DATABASE db_Demo
FROM DISK = ‘D:db_Test.bak’
WITH MOVE ‘db_Test’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.PCVARKSERVER\MSSQL\DATA\db_Test.mdf’,
MOVE ‘db_Test_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.PCVARKSERVER\MSSQL\DATA\db_Test_log.ldf’
–WITH REPLACE, MOVE
— SET database in MULTI User Mode
ALTER DATABASE SomeDB SET MULTI_USER
But it will throw error
The file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.PCVARKSERVER\MSSQL\DATA\db_Test.mdf’ cannot be overwritten. It is being used by database ‘db_Test’
Plz help me
This is because you already have db_Test.mdf file. just change the MOVE command and put new file name.
Thanks.. It’s work..!!
@krlsnry – That’s great to hear
Please do as step:
1. Creat database
2. Choose user Permission
4. Choose Overwrite
==> Done 100% ( SQL 2008 & 2008 enterprite), I think that’s done for 2012
First option works.
Thanks for the post. It works
Thanks for this comment @Suganya