Sqlserver-dba.com

SQL Server restore problem - Error 3154: The backup set holds a backup of a database other than the existing database


On SQLServer 2005 Standard Edition Service Pack 2  - we have a routine that backs up databases onto a dedicated drive. It is a full backup to disk. This routine does not drop the database

BACKUP DATABASE MY_DB TO  DISK = 'I:\backups\jvTest20081127.BAK' WITH NOFORMAT,COPY_ONLY, INIT,  NAME = MY_DB  , SKIP, NOREWIND,

NOUNLOAD,  STATS = 10;

 

 

A couple of weeks later , I needed one of the databases and attempted a standard RESTORE DATABASE
but without the WITH REPLACE parameter.

   RESTORE DATABASE MY_DB FROM DISK =  'I:\backups\jvTest20081127.BAK'


An error was encountered : Error 3154: The backup set holds a backup of a database other than the existing database
The error occurs because I was  trying to restore the database on another existing active database.


The solution is :

RESTORE DATABASE myDB
   FROM DISK = N'I:\backups\jvTest20081127.BAK' 
WITH REPLACE

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Posted by Jack Vamvas at 1:10 AM in Backup and Restore, SQL problems | Permalink | Comments (6) | TrackBack (0)

Technorati Tags: , , , , , , , , , , , ,

Or if you are using SQL Server management Studio then go to Options, while restoring the database and set "Overwrite Existing Database" checkbox as checked in Restore Options.

Posted by: Jawad | May 4, 2009 11:40:22 PM

Thanks... It helps a lot!

Posted by: Doug | Sep 17, 2009 6:22:22 AM

Innovative technology has all the answers to these problems and you only have to look for them.

Posted by: Cheap Computers Canada | Apr 1, 2010 1:56:57 AM

I am quite sure they will learn lots of new stuff here than anybody else!

Posted by: Resume Writing Services | Jun 10, 2010 6:19:11 AM

restore database dlf
from disk='D:\upback\advw.bak'
with replace ,password='@123'


hello frnz this is my query...when i executed it it is showing an error like this


Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' cannot be overwritten. It is being used by database 'AdventureWorks'.
Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf' cannot be overwritten. It is being used by database 'AdventureWorks'.
Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_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.

please do reply

Posted by: sushil | Oct 12, 2011 5:20:05 AM

Sushi, Check to see if there is a database (probably called AdventureWorks) . It has the files locked. Change your database name to the target database name - i.e Restore database AdventureWorks from disk='D:\upback\advw.bak'
with replace.
Backup the target database first

Posted by: Jack Vamvas | Dec 14, 2011 10:26:53 PM

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact