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

Search

Email +Jack Vamvas at jack@sqlserver-dba.com

SQLServer-DBA.com Links

Subscribe to newsletter

Recent Posts

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

31 December,2008 by Jack Vamvas

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

SQL Server - BACKUP LOG WITH NO_LOG - SQL Server DBA

BACKUP failed to complete the command BACKUP LOG - SQL ...

Differential backup cannot be restored . BACKUP with COPY_ONLY ...

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)

Searches: - Error 3154:, backup, backup, database, database, existing, holds, other than, problem, restore, Server, set, SQL

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.

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.

Search

Subscribe

Categories

SQLServer-DBA.com Links

Recent Posts

Read More

Verify your Comment

Previewing your Comment

Post a comment