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://blog.sqlauthority.com)
302 Comments. Leave new
This is very helpful.
Pinal Deva ki jai ho…… ,
I was trying to restore that database by wizard and also selecting Replace / overwirte than too its was going me Error:3154
And i tried by query it work perfect …. dont know how this happen but my problem get solved for now i will find the cause y such happen …..
ThnQ boss :)
cu soon
in TechDays :)
Thanks. I come across this error occasionally and I always forget the SQL statement to restore.
Hi Guys,
This is for all you techie’s who like me might have scratched their head for sometime.
Thanks for all the people who had posted in this page, reading your postings I could arrive at my solution.
I had to use a combination of both REPLACE and MOVE commands.
My posting is done based on SQL 2005. I hadn’t tested it in other versions.
My Situation:
I backed up a database in T-SQL, created a new DB with a diff name and used the backup file to restore onto the new DB and came up with the dreaded error – “The backup set holds a backup of a database other than the existing ‘xxx’ database.”
My Predicament:
I cannot use the Enterprise manager, because this is an application where the coding is done and it is a process that needs to create the database doing the above steps.
My Solution:
RESTORE DATABASE NewDBName FROM DISK = N’C:\OldDB.BAK’ WITH REPLACE, MOVE ‘OldDBLogicalName_data’ TO ‘C:\Location\NewDB_Data.mdf’, MOVE ‘OldDBLogicalName_Log’ TO ‘C:\Location\NewDB_Log.ldf’, STATS = 10
Hi pinal,
I need your help in SQL server restoring issue. Error is as follows.
“The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)”
I know that it is not possible to restore SQL server 2005 database in SQL server 2000 database.
Do we have any way to export the data from 2005 and import it to SQL server 2000?
Please assist me.
Dear All,
This post really saved a lot of time and got the solution in a minute.
Manoj
Hi All,
I tried to restore a .bak file from 2005 SQL server to 2008 SQL server, but the process errors out using the above method. I have got a list of errors, one of them is
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file “M:\MSSQL\MARKET\MARKET_primary_01.mdf” failed with the operating system error 3(The system cannot find the path specified.).
The process is referring to M:\MSSQL\MARKET, the location where the MARKET_primary_01.mdf was residing originally in the source server, whereas it should refer to the location where the destination sever is.(correct me if I am wrong)
Most of the errors are for the incorrect locations.
Can anyone help me out with this?
Thanks in advance.
Regards,
Litesh
Hi Pinal
1)How to restore the .bak file in sql server 2005..
But that .bak was created by using sql server 2000….
2) How to store the IP address..
1 restore database dbname from disk=’backup path’
2 Use varchar column
Hi Pinal,
1. I have 30 GB database, this database contain 3 mdf files and 1 log file. It’s possible to merge 1 mdf files. if possible how we can do it.
2.My drive free space contain 70 GB while i Restore 30 GB database It’s required nearly required 82 GB why it’s happen
Please guide on this………….
Thank you so much!
Thanks
Thanks a lot. It was of great help to me.
Thank you, this was a huge help :)
I was trying to restore a customers database and they only had a .bak file, so this solved the problem :D
Thanks
For me it worked with the WITH MOVE clause
RESTORE DATABASE [XXX]
FROM DISK = ‘D:XXX.bak’
WITH MOVE XXX’ TO N’D:MSSQLMSSQL.2MSSQLDATAXXX.mdf’
, MOVE N’XXX_log’ TO N’D:MSSQLMSSQL.2MSSQLDATAXXX.ldf’
, NOUNLOAD, REPLACE, STATS = 10
Cheers
U saved my life Thanks
on the money, thanks!
Why was SQL SMS not answering me what to do with target database?
u r a genious sir…
live long n happy life
:)
Thanks it worked
thank you
worked perfectly! thanks Pidal!
Thanks, it save my life!