SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database | SQL Server Journey with SQL Authority

Feeds:: Posts; Comments

« SQLAuthority News – Book Review – Programming SQL Server 2005 [ILLUSTRATED]

SQL SERVER – Introduction and Example for DATEFORMAT Command »

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)

About these ads

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 | 251 Comments

251 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 October 3, 2010 at 12:08 pm | Reply harish
  
  Hi pinal,
  
  i have dought in sql admin :
  
  1) how to check in sql server port number in command prompt.
  
  2)how to check database or table which extent is using , where can i find out , any query is there please tell me
  
  Thanks&Regrads
  harish
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 September 13, 2011 at 2:00 am | Reply Lea
  
  Thanks for the GUI (ie novice aka hack) user consideration, Arjun. Very helpful.
  - on April 3, 2013 at 10:16 am | Reply NitRa
    
    Thanks Arjun
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 June 27, 2011 at 6:16 pm | Reply haneen
  
  i read it lee .. thx
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 January 25, 2010 at 11:21 pm | Reply Dan
  
  Did you ever get your question answered? I’m having the exact same problem and can’t get it to restore because it is looking for the full text catalog location. Please help!
  - on February 9, 2010 at 10:27 pm | Reply Dennis
    
    @Dan Did you get a resolution? I’m having the same problem.
  - on February 9, 2010 at 10:55 pm | Reply Dennis
    
    I resolved this by renaming my mdf and ldf files to match the source mdf and ldf file names. I used the WITH REPLACE option, though I’m not sure if it was required in my case or not.
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 December 6, 2012 at 11:59 am | Reply anildesai
  
  @Pierre, Thanks a lot. You solved my problem :)
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 March 31, 2010 at 6:18 pm | Reply Madhivanan
  
  Take a backup of the existing database and restore it by giving new database name
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
on November 19, 2009 at 6:51 pm | Reply jody

Thanks Pinal , this script worked for me.

At first I thought the script was freezing because I ran it for 25 minutes and it didn’t do anything. I thought it was freezing up.

I finally added “STATS = 1″ to the script because I needed to see when the script completed at 1% intervals. It turns out that the database I was restoring was so big (60+ GBs) that the script ran for nearly 2 hours.

Thanks again!

-Jody
on November 26, 2009 at 12:20 pm | Reply M. G.

It works really great for me. Thank You
on November 26, 2009 at 1:18 pm | Reply Kapil Bhargava

Thanks Pinal Sir, it worked for me loads of thanks
on December 1, 2009 at 9:07 am | Reply Sourabh

Great post !!!!
on December 3, 2009 at 7:52 pm | Reply Soumik

Thanks much :)
on December 5, 2009 at 11:47 pm | Reply Michael A

Thanks for sharing your expertise, it helped me restore
my databases successfully.

:-)
on December 10, 2009 at 11:08 am | Reply Rohit Kandhal

Thanks alot !!
workedi n my case .:)
on January 7, 2010 at 10:39 am | Reply Dhanz

Excellent post!!!!!!!!!!!!!!!!!!
on January 21, 2010 at 7:02 pm | Reply Nicky

Thank you very much! :D It has solved my problem!
on January 27, 2010 at 2:15 pm | Reply Ngoc Phu

Thank you very much ! It works well.
on February 1, 2010 at 8:50 am | Reply span

Once again, you have provided a solution to another problem I encountered!

Thank you very much, and keep up the god work :)
on February 11, 2010 at 11:14 pm | Reply Jimmy Patel

Hi Pinal

hope u doing good, i used your code and also i tried to restore my backup with Overwrite flag, still having some issue.

Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘Database Name’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

thanks,
Jimmy
on February 12, 2010 at 2:42 am | Reply Andre

Thank you so much for this! :)
on February 12, 2010 at 4:36 pm | Reply Pinal Dave

Hello Jimmy,

First take the database in SINGLE_USER mode and then execute the restore command. For details please visit the below article:

http://mssqlonline.blogspot.com/2007/02/restore-database-backup-using-sql.html

Regards,
Pinal Dave
on March 1, 2010 at 2:21 pm | Reply Kenarina

Excellent Solution, thanks for the great tip !
on March 30, 2010 at 9:47 pm | Reply Joe Mad

I want to restore the database with a new name, so I can use it as my test database
- on March 31, 2010 at 6:21 pm | Reply Madhivanan
  
  Yes you can do it by giving different database name while restoring it
on March 31, 2010 at 7:27 pm | Reply ranjan

Really interesting…thanks a lot.
on April 5, 2010 at 12:03 pm | Reply prabhat

thank you sir it’s working.
on April 9, 2010 at 6:44 am | Reply Ben

WITH REPLACE – thanks! You’re in my favorites now.
on April 18, 2010 at 12:06 pm | Reply rakesh

thank u very much sir
it saved my time
on April 19, 2010 at 1:11 am | Reply Shashank Tagotra

gr8 man gr8
on April 26, 2010 at 4:23 pm | Reply ashwin

gr8, with using above code I restored database file of sql server 2005 in sql server 2008.
on May 20, 2010 at 3:37 pm | Reply Rahil Namayande

thank you for your interesting posts…
on May 26, 2010 at 8:54 am | Reply Don

thank you!! :)
on May 27, 2010 at 10:09 am | Reply ramesh

Server: Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file “G:\SQL DATA\MSSQL\DATA\material_data1.ndf” failed with the operating system error 3(The system cannot find the path specified.).
Server: Msg 3156, Level 16, State 1, Line 1
File ‘material_1_Data’ cannot be restored to ‘G:\SQL DATA\MSSQL\DATA\material_data1.ndf’. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
on May 27, 2010 at 4:28 pm | Reply Prakash

This is really helpful to take the backups.
on June 8, 2010 at 10:22 am | Reply kamini

Hello ,

i want to restore database in sql server but i have ony *.ibu
file.i have used this syntax but i am failed….

can you give me some solution how to restore this database?
on June 18, 2010 at 5:55 am | Reply @aspgod

Thanks a lot. you just help me again and again.

:D
on June 27, 2010 at 7:52 pm | Reply Shahzad Gul

Thanks man !!!
You really saved valueable time :)

Cheers !
on June 30, 2010 at 4:48 pm | Reply VikasN

Excellent,
on July 2, 2010 at 7:25 am | Reply Bill

Thanks. I knew it was simple; just needed the reminder
on July 6, 2010 at 4:06 pm | Reply Suresh N

This is very helpful solution to me.
on July 18, 2010 at 10:29 pm | Reply Mohammed

Thanks for your greatfull help realy it is too simple but for professionals like you maybe we are too beginners to find the solution in such way you do.
on July 22, 2010 at 2:08 pm | Reply Mudit

thanks a lot…this cud be best solution.
on August 3, 2010 at 3:13 am | Reply Gary Ramu

Saved my gourd. Had accidentally ran script which fouled up production database files in big way. Original restoration procedure failed. That simple code (we rarely use) restored , accountants are now back at work.
on August 12, 2010 at 3:38 pm | Reply Paul C

Thanks,
its great to have people like you around!
on August 20, 2010 at 4:07 am | Reply amit

Thanks a tonnnnnn……
on August 20, 2010 at 5:28 pm | Reply Gourav

Its working fine

Thanks

Thank you very much
on August 28, 2010 at 9:16 am | Reply Asim

Thanks a lot :)
on September 3, 2010 at 7:46 pm | Reply Alex Farias

If you use SQLExpress instead of applying these commands, follow these steps:

1-Select the base you created for the restoration right
2 – Tasks> Restore> Database
A new window will open.

1 – Select “From Device” and click on … to choose the origin
2 – Click Add
3 – Select aorigem Base and OK and OK
4 – Check the box where his base appears to be restored
5 – Go to Options and check the “Overwrite the existing base”

So far, this seems more complicated than just typing the command, but in practice it is not. It does not take even 5 seconds.
on September 3, 2010 at 7:48 pm | Reply Alex Farias

If you use SQLExpress instead of applying these commands, follow these steps:

1-Select the base you created for the restoration, and click the right mouse button
2 – Tasks> Restore> Database
A new window will open.

1 – Select “From Device” and click on … to choose the origin
2 – Click Add
3 – Select aorigem Base and OK and OK
4 – Check the box where his base appears to be restored
5 – Go to Options and check the “Overwrite the existing base”

So far, this seems more complicated than just typing the command, but in practice it is not. It does not take not even 5 seconds.

“The comment above is bad transalted, choose this one end ignore this line for post”
on September 17, 2010 at 7:45 pm | Reply Pradeesh

Thank you sir,
You saved my day
on September 22, 2010 at 6:23 pm | Reply Usha

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.
on September 24, 2010 at 12:41 am | Reply Mnaouar

Hi
I do not agree that all those answers are for the subject :
The backup set holds a backup of a database other than the existing database

Yes you could do : restore with replace

But the answer for the above subject is :
If you get that message “The backup set holds a backup of a database other than the existing database”

This is happening with a restore to SS 2005 from a SS2000 backup. This article says to use the ‘WITH REPLACE’ clause. The SS2005 management studio does not seem to allow this via the GUI.
on September 24, 2010 at 2:57 am | Reply nidhin Raj

Hi,

I am new to SQL Server.I have faced the same problem.When i used the query which you have given i got an error

Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘India’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please help me to resolve

Regards
Nidhin
on September 24, 2010 at 7:25 pm | Reply Ekta

Hi,

First of all..thanks a lot for this post..It helped me to restore.

I tried from GUI (slect override checkbox)..but didnt work.

Answer for last post – Connect to master database and then try this, it would work.

Rgds,
Ekta
on October 5, 2010 at 10:11 am | Reply Ven

Hello Alex Farias,

Great job.Double Thanks for the tips. It works.

Venki
on October 13, 2010 at 2:20 am | Reply Maghsoudfar

ThankYou,
Thanks,
Tnx………………………
It worked,…
on October 20, 2010 at 7:55 pm | Reply Deepali

Thanks for this beautiful little piece of code, saved a lot of effort!!!
on November 4, 2010 at 4:37 pm | Reply Deepak Lakkad

Thanks pinal
on November 4, 2010 at 8:59 pm | Reply Miguel

Thank you so much. Thank you for your time, for publish that kind of tips .
on December 3, 2010 at 11:32 pm | Reply Aziz

Dear Mr. Pinal,

I have a database in my organizaion. DB File Size is about 4GB and Transaction Logfile Size is about 82 GB. I am taking full backup of the database everyday at 12:10 AM by a job. When I tried to restore the database in another Server its after 48 hours… the Database restore is still going on. The Source Server is IBM x3600 8GB RAM M2 with 1TB Space and Target Server is IBM x3600 16GB RAM M3 with 1TB Space.

I also tried the following thing:
1. Backup only PRIMARY Filegroup on Target Server by
Backup Database FileGroup=’PRIMARY’ TO DISK=’C:\test.bak’
2. Copy test.bak from Source Server C: Drive to Target Server C:\
3. Restore that database on Target Server by
Restore Database FROM DISK=’C:\test.bak’

But still it shows me “The backup set holds a backup of a database other than the existing ” Message.

Could you please help

Thanks and Regards
Aziz
on December 4, 2010 at 2:47 am | Reply Dan

That worked great! Thanks!
on December 5, 2010 at 1:14 pm | Reply Nitin

Thank u so much…
on December 5, 2010 at 1:16 pm | Reply Nitin

Thank u so much your blog have solved my problem so many times.
on December 6, 2010 at 2:45 pm | Reply Ramaswamy

Thanks a lot.
on December 8, 2010 at 1:29 pm | Reply Uhai

how to restore sqb backup in sql . in this back up contain 3 mdf file and 1 log file .please guide me to restore
on January 7, 2011 at 5:11 pm | Reply Hari

Great! It worked for me…. Thanks a lot Pinal!!!!!!
on January 19, 2011 at 9:31 am | Reply Sivawut Srithammawut

This is very helpful.
on January 19, 2011 at 5:52 pm | Reply Ashish Amodia

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 :)
on January 25, 2011 at 8:37 pm | Reply Paul

Thanks. I come across this error occasionally and I always forget the SQL statement to restore.
on February 10, 2011 at 4:36 pm | Reply Arun

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
on March 1, 2011 at 7:42 pm | Reply hızlı okuma

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.
on March 12, 2011 at 12:13 pm | Reply Manoj S Nair

Dear All,

This post really saved a lot of time and got the solution in a minute.

Manoj
on May 23, 2011 at 3:33 pm | Reply Litesh Gawande

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
on June 6, 2011 at 9:44 am | Reply Ramya

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..
- on June 7, 2011 at 1:16 pm | Reply Renu
  
  hi ramya
  
  u create the sqlscript of that .bak file in sql server 2000 and execute the script in sql2005.. itz wil be work fine….
  
  Refer the below link..
  http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
- on June 10, 2011 at 3:46 pm | Reply madhivanan
  
  1 restore database dbname from disk=’backup path’
  2 Use varchar column
on June 13, 2011 at 10:51 am | Reply Uhai

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………….
on June 14, 2011 at 12:29 am | Reply sergioalfredoag

Thank you so much!
on June 28, 2011 at 6:02 am | Reply Haroon

Thanks
on July 2, 2011 at 8:34 pm | Reply Sridhar Jammalamadaka

Thanks a lot. It was of great help to me.
on July 6, 2011 at 3:41 pm | Reply Bent Pedersen

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
on July 20, 2011 at 8:50 am | Reply jamezu

Thanks
For me it worked with the WITH MOVE clause

RESTORE DATABASE [XXX]
FROM DISK = ‘D:\XXX.bak’
WITH MOVE XXX’ TO N’D:\MSSQL\MSSQL.2\MSSQL\DATA\XXX.mdf’
, MOVE N’XXX_log’ TO N’D:\MSSQL\MSSQL.2\MSSQL\DATA\XXX.ldf’
, NOUNLOAD, REPLACE, STATS = 10

Cheers
- on September 11, 2012 at 7:05 pm | Reply rajendra
  
  U saved my life Thanks
- on February 5, 2013 at 9:34 pm | Reply mbowles201
  
  on the money, thanks!
on August 17, 2011 at 12:34 pm | Reply Sta

Why was SQL SMS not answering me what to do with target database?
on September 20, 2011 at 11:51 pm | Reply mohammad usman shad

u r a genious sir…
live long n happy life
:)
on November 23, 2011 at 10:31 am | Reply halit

Thanks it worked
on January 3, 2012 at 8:56 pm | Reply jean

thank you
on January 23, 2012 at 10:44 am | Reply ER

worked perfectly! thanks Pidal!
on January 25, 2012 at 10:52 pm | Reply Rubi86

Thanks, it save my life!
on February 1, 2012 at 12:38 pm | Reply Pradeep

Execellent one..
on February 9, 2012 at 7:53 pm | Reply straton nahimana

Thanks you are a star…
on February 13, 2012 at 6:48 pm | Reply KevDeta

Thank a lot ! With a tiny REPLACE that works great !
on February 22, 2012 at 8:50 pm | Reply Matias

Funcionó de 10. Gracias
on March 1, 2012 at 1:03 pm | Reply Custom Sotware Australia

Thanks; Such a simple fix; I have heard of people doing all sorts of things like attaching the .mdf files etc but this worked perfectly :)
on March 1, 2012 at 11:40 pm | Reply Austin Macdade

Please update the installation instructions to use the SQL command
RESTORE DATABASE ScotlandYard FROM DISK = ‘[path to backup folder]\ScotlandYard.bak’ WITH REPLACE
as the instructions posted don’t work.
on March 3, 2012 at 10:58 am | Reply Braja Majumder

You are genius….
on March 5, 2012 at 12:59 pm | Reply Kunal Goel

Hi Pinal,
I followed the same as mentioned by you. But I am still getting the same error message
on March 7, 2012 at 8:07 am | Reply TechBang

I have trouble with multiple restore, get the media issue

[Msg 3231, Level 16, State 1, Line 1 The media loaded on "D:\db\db1.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.]

Trying to restore one bak file with REPLACE option is replacing the previously restored tables. But without REPLACE option get the following error

[Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'SIMSDB' database.]

Any ideas?
on April 10, 2012 at 12:03 pm | Reply Waseem

Man, you are amazing, i find the answers always in your blog :), thanks so much
on April 18, 2012 at 11:46 pm | Reply sathwick

Thanks
on April 20, 2012 at 7:23 pm | Reply koton

Thank you.
on May 11, 2012 at 8:38 pm | Reply majed

hi pinal i had tried to restore the database by using your way but it give this error msg 3102 level 16,state 1,line 2
restore cannot process database ‘mydatabase’ because it is use by this session it is recommended that the master database be used when performing this operation 3013 level 16 state 1, line 2
restore database is terminating abnormally
on May 16, 2012 at 12:58 pm | Reply Luqman Baloch

Thanks its really helped
on May 17, 2012 at 9:04 pm | Reply Reza

I’ve got same error,
I fixed it by selecting the Overwrite the existing database option
on May 31, 2012 at 9:18 pm | Reply CodeGeek

Hi Pinal,

i am new to sql development and I was trying this..
when I replaced “With move” with “with Replace” it says
Replace is not a recognized option.
where did I go wrong..
on June 16, 2012 at 1:43 am | Reply Gnro

Worked great! So simply explained. You are the best!
on June 29, 2012 at 4:13 pm | Reply Chris

Thank you very much!
on July 19, 2012 at 1:28 am | Reply jmc_nosegoes

Worked great! Thank you sir.
on October 2, 2012 at 10:27 pm | Reply aneu1010

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I’m getting this error. Any thoughts? I’m not running anything on this DB.
on October 23, 2012 at 10:22 pm | Reply Shaikh Asif

Worked great! Thank you sir.
on November 20, 2012 at 8:40 am | Reply jai

Thank you. There is several times that you blog was save my time…
on November 23, 2012 at 7:21 pm | Reply ABI

thanks a lot..
on November 25, 2012 at 6:55 pm | Reply Omar

thank you it’s helpful
on January 3, 2013 at 4:44 am | Reply Big Dave

Thanks mate, helped me get around an issue.
on January 16, 2013 at 6:23 pm | Reply Nur

I use this script to drop active connection before the restore script

USE [master]
GO
ALTER DATABASE [X] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

cheers
on February 7, 2013 at 12:31 pm | Reply ashamideAngel

Wonderful! Wonderful! Wonderful!…thanks for your help….i deleted the database folder causing the conflict and just restore the backup…i did not use command, just the GUI….. thanks
on February 8, 2013 at 2:10 pm | Reply Nandha

Hi Pinal,

As per your advise i have given the correct path to restore the database,but its not succeed.
here my SQL query to restore the database is

restore database customer_test
from disk = ‘D:\DB BAK\customer.bak’ with replace

after execute this query i got an error :

Msg 3203, Level 16, State 1, Line 4
Read on “D:\DB BAK\customer.bak” failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

The database size is : 13GB
SQL Server Version : 2008R2

kindly give me suggestion in this
- on February 13, 2013 at 2:00 pm | Reply madhivanan
  
  Is that a valid backup? How was the backup done?
on February 11, 2013 at 3:23 pm | Reply Osama Shafqat

Thank you very much for such a great help
on February 25, 2013 at 6:03 pm | Reply vrishali

vrish

1) i want to create a procedure to copy 1 database schema including all objects (views,index,procedures,tables,table data etc.) to another database schema within 1 server without generating script..but with SMO object
2) after getting all schema i want to get all updates which is done in source database without droping the previous data and schema..with SMO object..
plz let me knw as early… thank you..
on March 18, 2013 at 7:22 am | Reply JT

Fantastic – as alway, clear and simple instructions. Thank you
on March 21, 2013 at 10:35 am | Reply Abdirahman Haji Mohamoud

Thank you pinal
on April 4, 2013 at 3:08 am | Reply Randy

Thanks, It worked like a charm!
on May 3, 2013 at 9:31 pm | Reply Valerio

It works!!! Thank you!
on May 10, 2013 at 5:34 pm | Reply Vishnu Singh

v v thanxs sir
on May 16, 2013 at 11:06 am | Reply Sergio (@casertillo)

Man!! you are a genious!!!! thanks!!

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Connecting to %s

Community Initiatives
About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.

Follow @pinaldave
Send +Pinal Dave an email at pinal@sqlauthority.com
- 63,532,072 (63 Million+)
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 44,162 other followers
SQL in Sixty Seconds
SQL Books

Amazon | 1 | 2 | 3 | 4 | 5
Flipkart | 1 | 2 | 3 | 4 | 5

SQL Interview Q and A
Amazon | Kindle Flipkart
SQL Wait Stats
Amazon | Kindle Flipkart
Funny Index Video
SQLAuthority Links

My Homepage
Windows Live Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads

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

Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
> SQL Interview Q & A <
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
About Nupur Dave

Nupur Dave loves technology simply because it makes life more convenient. She is devoted to technology because it touches our heart makes our daily lives easier. Among the many technological programs she uses and embraces Windows Live most because she can do lots of things with ease – from photo management to movies; business emails to personal social media connections.
Top 3 Commenters

  2857 - Madhivanan
  474 - Imran Mohammed
  301 - Ramdas Jaya

Blog at WordPress.com.

Theme: Customized MistyLook by WPThemes.

Send to Email Address Your Name Your Email Address
Cancel
Post was not sent - check your email addresses!

Email check failed, please try again

Sorry, your blog cannot share posts by email.

%d bloggers like this: