Our Jr. DBA ran to me with this error just a few days ago while restoring the database.
Error 3154: The backup set holds a backup of a database other than the existing database.
Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.
Fix/WorkAround/Solution:
1) Use WITH REPLACE while using the RESTORE command. View Example
2) Delete the older database which is conflicting and restore again using RESTORE command.
I understand my solution is little different than BOL but I use it to fix my database issue successfully.
3) Sample Example :
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
302 Comments. Leave new
thanks. it worked for me finally.
Works like a charm.. Thanks
Dave, you saved me again. you’re Awesome.
Thnx a lott. saved me from a lott of work …
Thanks Man,You rock!!
hello,
Can any one tell me the erroe occuring to restore the database in sql server
script :
RESTORE DATABASE TESTProjectENGPAY
FROM DISK = ‘F:\New folder\BACKUP.bak’
WITH REPLACE
Error
Msg 1834, Level 16, State 1, Line 1
The file ‘E:\MSSQL\PROJECTENGPAY.mdf’ cannot be overwritten. It is being used by database ‘PROJECTENGPAY’.
Msg 3156, Level 16, State 4, Line 1
File ‘PROJECTENGPAY’ cannot be restored to ‘E:\MSSQL\PROJECTENGPAY.mdf’. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file ‘E:\MSSQL\PROJECTENGPAY_log.ldf’ cannot be overwritten. It is being used by database ‘PROJECTENGPAY’.
Msg 3156, Level 16, State 4, Line 1
File ‘PROJECTENGPAY_log’ cannot be restored to ‘E:\MSSQL\PROJECTENGPAY_log.ldf’. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I didn’t understand ur post plz post clearly
Thanks
this solution not work because i get this error message
”
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file “J:sql_dataEmpservDB_BK1_1.ndf” failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 4
File ‘EmpservDB_FG1’ cannot be restored to ‘J:sql_dataEmpservDB_BK1_1.ndf’. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
Pls Succession For Me
Thanks Pinal, helped me a lot. :)
great man! Thanks, works totally!
Very nice solution. Worked without any hitch
You rule. Like so many solutions, elegantly simple once you know how.
Thanks it worked
Excellent !!!! You save mi head!!!
thanks a lot it helped me :)
This solution was not working for me. I had an empty database & try to restore with replace. It was throwing same error. Than I deleted empty database & tried to restore the bak file, bak file created the database itself & restored it successfully.
By doing above solution i am getting error
Msg 3241, Level 16, State 7, Line 1
The media family on device ‘f:\TSPL_CRM.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I am getting error in sql2008 by restoring database and i am not able to do in windows authentication.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) .
i have installed sql2008 setup i am not able to login in sql by sql server mode. do i have to install anything else
thanks very much
Your welcome Jomy.
iwant to restore database to sql 2005.
sql 2005 don’t have WITH REPLACE option
i’m getting the same error
how can i restore my backup to sql 2005
WITH REPLACE option exists in SQL 2005
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms186858(v=sql.90)