SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database « Journey to SQL Authority with Pinal Dave

Feeds:: Posts; Comments

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

September 27, 2007 by pinaldave

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 (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 122 Comments

122 Responses

on March 6, 2008 at 3:18 am | Reply Shlomo

This was a huge help.
But I think you forgot the slash in the example
FROM DISK = ‘C:\

(it didn’t work for me until I added the slash.)

Anyways, thanks a lot, you saved me from having to go to my senior dba
on March 7, 2008 at 10:46 am | Reply Lalita

Charming little piece of code!! thanks!
on March 14, 2008 at 10:25 am | Reply Ibrahim

great!
on March 19, 2008 at 11:47 am | Reply Venki

Great! thanks.
on March 23, 2008 at 3:19 am | Reply Arjun

check the ‘Restore on Existing Database’ option box if you are using the GUI
on March 24, 2008 at 2:53 pm | Reply Kranthi

Nice post. Could have stopped our Go live ! Thanks a lot.
on March 28, 2008 at 12:53 am | Reply Reymes

Good for you man!!!!! A question: Why it did not work when I tried to do it through the RESTORE option available in Microsoft SQL 2005? The script generates “WITH FILE = 1, NOUNLOAD, STATS = 10″ instead of “WITH REPLACE”. Did I miss anything in the set up?
on April 16, 2008 at 1:15 am | Reply Hector

Thanks man, I was trying to restoring using the wizard, but it work beautifully, with your code
on April 18, 2008 at 1:32 am | Reply Robert

Thanks for this. Seems small to one who knows but I had some pain trying to restore a db until I found this. Thanks
on April 24, 2008 at 5:54 pm | Reply pian

This helps me a lot!
on April 30, 2008 at 7:22 am | Reply Manish MISTRY

Great! it helped!
on April 30, 2008 at 9:02 am | Reply Judy M

you just saved my life! Thanks!!!
on May 12, 2008 at 10:26 am | Reply Gurpreet Singh

Thankyou so much !! gr8 help
on May 12, 2008 at 5:08 pm | Reply None

Thank you so much, it worked great
on May 28, 2008 at 12:35 pm | Reply Sampath Dassanayake

You can also go to the options page of the Restore Data Base dialog (In Management Studio Express) and select the option, Overwrite Existing Database.
on May 29, 2008 at 11:54 pm | Reply Amit Joshi

Pinal you are great!!!!!

Three cheers for Pinal….right from my heart…..

Thank you very much….
on May 30, 2008 at 11:59 am | Reply Fakeer Mohamed

Hi Pinal,

Thank you very much…

It’s working fine….

Simply super coding…
on June 2, 2008 at 6:17 pm | Reply Ron

Great article, especiall full example with MOVE. BTW, is there some workaround in order not to use all the files-to-move? In my case there was 5 files or so. Is there some way to point all of them to some default location?
on June 3, 2008 at 4:48 pm | Reply Elisha Eshed

Thanks alot man, helped me a lot :)
on June 18, 2008 at 1:16 am | Reply Alton

I have tried to use the GUI, and the ‘Restore on Existing Database’ option, but I still get the error.
on June 18, 2008 at 3:09 pm | Reply Balasubramaniyam

Thank You very much. This article helped at right time
on June 18, 2008 at 7:46 pm | Reply gilad

you are a genius, problem SOLVED !
on June 23, 2008 at 10:39 am | Reply markpith

Thanks a lot for this post. People using the GUI should tick the following option: “Overwrite the existing database”. (SQL Server Management Studio Express)
on June 25, 2008 at 4:19 pm | Reply Ratish

Thanks a lot for the Article. It saved a hell lot of time.
on June 30, 2008 at 2:56 pm | Reply Harsh

thanks a lot…
on July 2, 2008 at 1:58 pm | Reply Sumanta

really helpful!!! thanks a lot…
on July 4, 2008 at 11:48 pm | Reply SergioF

A lot a thank!!
on July 19, 2008 at 12:30 pm | Reply Sumanth

THANK YOU VERY MUCH PINAL

U SOLVED MY PROBLEM IN A MATTER OF SECS

U r the TOUGHEST person in SQL SERVER
on July 22, 2008 at 4:44 pm | Reply Earl

You are the man!!!!!!!!!!
on July 29, 2008 at 9:00 pm | Reply keyur

thanks a lot..
on July 31, 2008 at 2:01 pm | Reply Andras

Great piece of code. Was almost convinced that MS had put a restriction in there…

Nice!
on August 7, 2008 at 3:52 am | Reply Arun

Thanks a lot…!!!!
on August 7, 2008 at 4:24 pm | Reply Chandrakat Garje

Thanks a lot ……………..
on August 8, 2008 at 5:11 am | Reply Brian

Thanks…developers aren’t DBA’s…saved my rear!
on August 14, 2008 at 2:47 pm | Reply Lode Desplancke

tsss, amazing what this little query can do :) saved me a phonecall and alot of time, thanks alot :)
on August 19, 2008 at 11:10 pm | Reply Chava

Excellent!! …. thanks Pinal!
on August 25, 2008 at 5:20 pm | Reply Nithya Mohanrajh

Thanks a lot…It was a magical code..
on September 4, 2008 at 2:02 am | Reply sree

Thanks a lot! I could do it from the GUI of SQL Mgt.. but this worked just great!
on September 4, 2008 at 2:03 am | Reply xplo

Thanks a lot! I coudnt do it using GUI but this didit!
on September 9, 2008 at 2:05 am | Reply Twetee

Hi..

This really helped me!!!! Thank you soo much!!!!
on September 10, 2008 at 11:39 am | Reply kajal

hi…
Thank u very much…. :)
on September 10, 2008 at 6:04 pm | Reply Kumar Ashish

Thank you!!!
on September 12, 2008 at 5:14 pm | Reply Tony

Many thanks for your help :)
on September 14, 2008 at 7:04 am | Reply amrelgarhy

Great, saved hours of recreating the database table by table again, and also i said “oh, hope i knew that before, and didn’t waste …..”

Thank you :)
on September 22, 2008 at 6:45 pm | Reply Juwel

Fantastic ! Thanks a ton !!!

– Juwel
on October 6, 2008 at 9:53 pm | Reply lee

If you are using the wizard you can achieve exactly the same by clicking the radio button that says “restore over the existing database” (or similar) it’s the top option on the options bit … but nobody’s gonna read my comment it’s too low on the page!
on October 9, 2008 at 5:21 am | Reply Jorge

Great. It worked for me Thanks
on October 14, 2008 at 1:15 pm | Reply Umash

great work man,
It simplifies my search
on October 14, 2008 at 4:10 pm | Reply Miss Shaikh

Hi Pinal Dave,

And thanks Pinal Dave too much you know why . b/c when ever i have problem with sql server query i search in your site and i got the rite solution :) .
i m happy to see you when ever i see your site.

i have RESTORE DB in sql server 2000 so i use your this solution.

RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\BackupAdventureworks.bak’
WITH REPLACE

it is very halp full for me Thanks again Dave :)
ok
Take care
bye bye
on October 17, 2008 at 7:49 am | Reply elipaqui

Thanks man its a good code
on October 21, 2008 at 5:29 pm | Reply d.chandrashekar

hi,
thanks for the response.
right now i am searching for sql dba openings.
can any body help me the important and frequent interview questions on core sql dba.

thanks
on October 22, 2008 at 12:01 pm | Reply Abood Abumaree

great thanks , and i wasn’t expect this solution will be solve my problem.
on October 23, 2008 at 4:27 am | Reply Ahmad

Great Idea, but if I want to restore without replacing the existing database ?? I want to insert tables inside the backup file to be added to the tables already there in the existing database !!
Any help ?
on October 26, 2008 at 8:56 am | Reply Nick

Many thanks Pinal, good things always comes in small packages…this query solved my problem…cheers:)
on October 31, 2008 at 3:59 pm | Reply Tarik

It Worked ! Thanks :D
on November 2, 2008 at 1:21 am | Reply Zurpit

Worked like a charm! Thanks!
on November 9, 2008 at 12:31 am | Reply Kapil

These 2 lines of codes save my lots times, whenever i goes to restore dayabase i always face this problem … I search problem on google and always yout these lines works ,.

Thanks a ton
on November 10, 2008 at 1:40 pm | Reply JensAnders

Thanks a lot!
on November 12, 2008 at 2:05 pm | Reply limmy

great code. huge thanks!
on November 17, 2008 at 12:11 am | Reply jonathan parker

hey thanks for the great tip! was a big help — jp
on November 17, 2008 at 7:41 pm | Reply Bill C

Thanks a bunch for the tip!
on November 27, 2008 at 5:54 pm | Reply Nusrath

Hi Pinal,
I took a full back-up from a server and tried to restore it on another server by creating a new database.

Since it was a full-back up, I had three files

abc.mdf
abc_log.ldf
sysft_abcCatalog

When I try to restore, the other machine did not have full-text installed. Thus I went for partial restore with replace option enable…. but the replace does not work.

I get the error Error 3154: The backup set holds a backup of a database other than the existing database. ….. even through I have written REPLACE option in the restore query:

Below is my query:

RESTORE DATABASE XYZ
FROM DISK = ‘C:\nusrath\abc.bak’
WITH REPLACE,
FILE=1,
PARTIAL,
MOVE ‘abc’ TO ‘c:\DATA\XYZ.mdf’,
MOVE ‘abc_log’ TO ‘c:\DATA\XYZ_log.ldf’,
NOUNLOAD,
STATS = 10

How should I work around ? Please help.

Thanks,

Nusrath
on December 3, 2008 at 9:45 pm | Reply Dimonico

I was just able to migrate a 2000 sql database to a 2008 with this little tidbit.

THANX!
on December 16, 2008 at 1:52 pm | Reply Piyush Chasta

H! Pinal , thanks a lot .i tried da way u suggested . i am able to create database from my backup file . but still getting db blank with no tables . i am using ms-sql 2005 server.
could any one suggest me whats the issue i m facing .
i am doin like”RESTORE DATABASE RelinkDB
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\RelinkDB.bak’
WITH REPLACE”
on December 16, 2008 at 9:29 pm | Reply Muzammil

Thanks mate. Your post was quite useful.
on December 18, 2008 at 3:53 pm | Reply Luke

Great hint. Cheers!
on January 13, 2009 at 11:44 am | Reply Shaghayegh

THanxxxxxxxxxxxxxxxxxxxx a lot!
on January 19, 2009 at 10:06 pm | Reply JayPad

Thank you!!!!! I don’t think i’ve ever had an issue resolved so fast and simply!!!
on January 21, 2009 at 9:05 pm | Reply David

Thanks for this, I was having the same problem, but that has fixed it!
on January 22, 2009 at 9:04 am | Reply Dax Davis

Thank you. That was a big help.

Dax
on January 28, 2009 at 12:58 am | Reply Gopal

Great Article! It helped us a lot. Thank you very much!
on January 28, 2009 at 9:22 pm | Reply m3nd4

Thanks Man! :D
on February 9, 2009 at 10:04 pm | Reply Shantanu

This is really a great work around and it resolved the issue.

Thanks a lot Pinal
on February 18, 2009 at 11:58 pm | Reply Rob

Thanks for the tip, Pinal. I’ve had to use it on several occasions.
on February 24, 2009 at 7:26 pm | Reply Pierre

Hello Nusrath

I had exactly the same issue. It seems to happend when we make a backup of several databases appendes on a same bak fIle.
In my case I had one backup file named J04Vendredi.BAK on which where 4 databases backup
1-master
2-msdb
3-lienWinXS
4-aeosdb

I wanted to restore aeosdb on a database called aeosdbLFSB on the target server located physically ON

C:\Data\aeosdbLFSB.mdf’,
C:\Data\aeosdbLFSB_log.ldf’,

After 2 hours of unsuccessfull tries, searches on the net, in the doc and a lot of dirty words I got it that way:

You have to know what was the name of the logical file on the source server with

RESTORE FILELISTONLY
FROM disk = ‘C:\LFSBVM002\J04Vendredi.BAK’ (replace with your path and filename

WITH file = 4
(You have to specify the sequence number where is the wished database !!!)

The first colum showed the logical names of the source server
-aeosdb_dat
-aeosdb_log

Then, I had to write the SQL statement below
RESTORE DATABASE aeosdbLFSB
FROM DISK = ‘C:\LFSBVM002\J04Vendredi.BAK’

WITH
REPLACE,
MOVE ‘aeosdb_dat’ TO ‘C:\Data\aeosdbLFSB.mdf’,
MOVE ‘aeosdb_log’ TO ‘C:\Data\aeosdbLFSB_log.ldf’,

FILE=4
on March 5, 2009 at 6:21 pm | Reply khanhpt

Thank you very much!
on March 7, 2009 at 11:42 am | Reply Mohamed Ridvan Shaikh

Thanks alot!!! Your code very useful…
on March 12, 2009 at 11:51 pm | Reply Amit

Thanks a lot. Worked in minutes !!
on March 24, 2009 at 4:19 am | Reply APR

You saved me a lot of time. Thanks!!!
on March 24, 2009 at 7:33 am | Reply richard

cool, solve my issue:
RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\BackupAdventureworks.bak’
WITH REPLACE
on March 30, 2009 at 12:44 pm | Reply Senthil Kumar

Thanks… I restored the database correctly… Once again thanks…
on March 30, 2009 at 5:24 pm | Reply Rana

Thanks Alot!

Really Greate and Wonderful
on April 7, 2009 at 8:00 pm | Reply Francisco

Hi,
For developing/testing purposes with original data I need to restore / copy data from one database to another with same database structure (tables, SPs, views, etc) but a different database name. from a backup file from original.
What is the best approach for this? CAn I use the restore command for this?
on April 11, 2009 at 3:42 pm | Reply Jemson Sentillas

Thanks for the article…
Have a nice day.
on April 21, 2009 at 5:26 am | Reply varumr

It worked!!
Thanks
on April 21, 2009 at 7:32 am | Reply Grateful

U DA MAN
on April 22, 2009 at 2:15 am | Reply Todd

Thanks Pinal. You have very good common sense in programming. Now let’s see what cool chakra graphic I get!
on April 23, 2009 at 8:25 pm | Reply Jesus

Hi, I got a question…I backuped my database from an Win2003 Server English Edition and when I try to restore the database into my development enviorment (wich is an XP Professional Spanish Edition) it shows an error like this:
Mens. 5133, Nivel 16, Estado 1, Línea 1
Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDataBase.mdf” failed with the operating system error 3(El sistema no puede hallar la ruta especificada.).
Mens. 3156, Nivel 16, Estado 3, Línea 1

Of course I dont Have “Program Files”, in spanish it is called “Archivos de Programa” I dont how I can change that..hope you can help me…Thanks..
(by the way, El sistema no puede hallar la ruta especificada. = The system could not find the specified path.)
on April 24, 2009 at 10:48 am | Reply BalaMurugan

hai,

i tried many time to restore backup file through GUI restore.
i wasn’t able to restore.
i tried your query within second finished.

Many Thanks,

Bala
on May 15, 2009 at 1:25 pm | Reply Paolo

Thank you so much, your solution worked for me!!
on May 28, 2009 at 3:27 am | Reply husna

thanks a lot, this was the my problem.
this solved my problem..
on June 3, 2009 at 5:47 pm | Reply nikhat

thank a lot
on June 18, 2009 at 9:46 pm | Reply Radhesham Shah

Great!!! Works like a charm :)
on June 20, 2009 at 4:33 pm | Reply Sunny Khetarpal

Sorry, it doesnt work for me please help…
on June 26, 2009 at 12:56 am | Reply Pavan

Thank you very much for your simple example. It works
on June 27, 2009 at 11:34 am | Reply Russ

Another individual happy to have stumbled upon your post. Thanks for sharing.
on June 30, 2009 at 3:51 am | Reply Simon Brown

Thank you sir! I can go to bed now!!
on July 3, 2009 at 2:13 pm | Reply Gurvinder

Thanks a lot. It work !!!!!!!!!!!
on July 14, 2009 at 8:48 am | Reply Dmitry

Thanks a lot for this is very helpful
on July 16, 2009 at 10:49 pm | Reply Ahmad

You’re the king man, a great piece of code that helped me a lot. Thanks kindly
on July 27, 2009 at 2:14 pm | Reply Arno

thnx
on August 1, 2009 at 6:22 am | Reply Didier

Excellent! Thanks.
on August 5, 2009 at 10:49 am | Reply Kalpana pathak

Magic…..
on August 16, 2009 at 11:42 pm | Reply Mandar

thank you pinal

the simple code was easy to use and it worked like a charm

thanks
on August 28, 2009 at 12:27 am | Reply john

WOW, you saved a life out there somewhere in Atlanta man…. I was dead before this.. you win two internets!
on August 29, 2009 at 8:01 pm | Reply Michael

Thanks. This tip should be on the main page of Microsoft SQL Server issues?

Why isn’t it there yet?
on September 6, 2009 at 7:32 am | Reply Isaacote

Thank you very much, you saved my ass.
LOL
on September 16, 2009 at 8:16 pm | Reply jon

hay thanx it really helped me alot
on September 17, 2009 at 1:55 am | Reply Tmkhan

Great Work…… very helpful and efficient technique !!!!!!!
on September 19, 2009 at 11:32 pm | Reply lian

Thank you Thank you!!!
on September 19, 2009 at 11:33 pm | Reply lian

Your my idol!!
on September 22, 2009 at 9:25 pm | Reply Gopal

Thank you very much! Great Help!
on October 6, 2009 at 1:23 am | Reply Ijeoma

Thanks a bunch cos FIX No 1 solved my problems perfectly.
on October 13, 2009 at 12:34 pm | Reply Gyani

Is this possible to replace filegroup with restore command with replace
on October 15, 2009 at 8:45 am | Reply Bleak

Mate,

That is golden

Nice Work
on October 22, 2009 at 10:03 pm | Reply Nisha

Super. Worked like magic. Thanks a lot!!!!
on October 28, 2009 at 6:53 pm | Reply Ian

Is there a way to delete/drop a database backup from the tape device? Please kindly advise.

thanks.
on October 29, 2009 at 3:37 pm | Reply prateek

Thanks Pinal….Keep up this nice work…..cheers
on October 31, 2009 at 9:27 am | Reply Prad

Hi

Thanks for the answer, mate. It was spot on. For newbies like me this was a great help

Cheers, Prad
on November 6, 2009 at 5:13 pm | Reply Phil

Saved me a lot of trouble, thanks
on November 12, 2009 at 9:13 pm | Reply remco

Thanks! worked for me!
on November 13, 2009 at 10:31 pm | Reply Heslous

For SQL2005 do not use ‘WITH REPLACE’ but only ‘REPLACE’ indeed, the word WITH must not be present

Leave a Reply

About Pinal Dave

Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1100 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.
Blog Stats
- 10,888,744 Readers
SQLAuthority Links

My Homepage
My Resume
My Other Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads

Script Bank
Favorite Scripts
All Scripts - 1
All Scripts - 2

Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
SQL Interview Q & A
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
Top 7 Commenters

  357 - Imran Mohammed
  135- Brian Tkatch
   55 - Tejas Shah
   54 - Jacob Sebastian
   47 - Jerry Hung
   46 - Kuldip Bhatt
   42 - Ashish Gilhotra
Categories
- About Me (61)
- Best Practices (91)
- Business Intelligence (14)
- Data Warehousing (30)
- Database (269)
- DBA (125)
- MVP (98)
- Poll (5)
- Readers Contribution (37)
- Readers Question (44)
- Software Development (67)
- SQL Add-On (91)
- SQL Backup and Restore (54)
- SQL BOL (10)
- SQL Coding Standards (20)
- SQL Constraint and Keys (51)
- SQL Cursor (28)
- SQL Data Storage (45)
- SQL DateTime (37)
- SQL Documentation (219)
- SQL Download (222)
- SQL Error Messages (125)
- SQL Function (124)
- SQL Humor (25)
- SQL Index (97)
- SQL Interview Questions and Answers (58)
- SQL Joins (63)
- SQL Optimization (66)
- SQL Performance (222)
- SQL Puzzle (25)
- SQL Security (119)
- SQL Server DBCC (42)
- SQL Server Management Studio (28)
- SQL Stored Procedure (101)
- SQL String (19)
- SQL System Table (50)
- SQL Trigger (24)
- SQL User Group (48)
- SQL Utility (126)
- SQL White Papers (37)
- SQLAuthority (374)
  - SQL Training (1)
  - SQLAuthority Author Visit (80)
  - SQLAuthority Book Review (18)
  - SQLAuthority News (344)
  - SQLAuthority Website Review (29)
- SQLServer (75)
- Tech (912)
  - Pinal Dave (902)
  - SQL Scripts (531)
- Technology (1155)
  - SQL (1155)
  - SQL Authority (1155)
  - SQL Query (1155)
  - SQL Server (1155)
  - SQL Tips and Tricks (1155)
  - T SQL (1155)
Top Posts
Authors
- pinaldave
Archives
Pages
Category Cloud
About Me Best Practices Database DBA MVP Pinal Dave Software Development SQL SQL Add-On SQL Authority SQLAuthority Author Visit SQLAuthority News SQL Documentation SQL Download SQL Error Messages SQL Function SQL Index SQL Joins SQL Optimization SQL Performance SQL Query SQL Scripts SQL Security SQLServer SQL Server SQL Stored Procedure SQL Tips and Tricks SQL Utility Technology T SQL
September 2007

M T W T F S S

« Aug Oct »

1 2

3 4 5 6 7 8 9

10 11 12 13 14 15 16

17 18 19 20 21 22 23

24 25 26 27 28 29 30