Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak.

I have a database with the name test2 which is the same database, but the data changed.

I want to restore test.bak over test2 database.

I am always getting the error:

Error 3154: The backup set holds a backup of a database other than the existing database.

I tried:

  1. I right-cliked on test2 -> Restore database -> From device

    I chose test.bak and checked With Replace but I get the error.

  2. Then I tried to right-click on test2 -> Restore file and filegroups

    I chose test.bak and checked With Replace but I get the error.

I can delete my old database and then restore my backup with the right name, but when I was using SQL 2008, I had no problem restoring over an existing database.

It seems that since I use SQL2012, I get this error a lot!

share|improve this question
add comment

migrated from stackoverflow.com Jun 20 '13 at 15:30

This question came from our site for professional and enthusiast programmers.

5 Answers

up vote 5 down vote accepted

You should use WITH REPLACE and in general avoid using the point-and-click thingies in Management Studio - they're inflexible and often have bugs.

This worked for me:

USE [master];
GO

CREATE DATABASE test;
GO

CREATE DATABASE test2;
GO

BACKUP DATABASE test TO DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION;
GO

RESTORE DATABASE test2
  FROM DISK = 'c:\temp\test.bak'
  WITH REPLACE,
  MOVE 'test' TO 'c:\temp\test2.mdf',
  MOVE 'test_log' TO 'c:\temp\test2.ldf';

Also you should make sure when you backup databases you use WITH INIT and/or don't point the device at a file that already contains a backup (since it might not be the same database you're backing up now - especially if you reuse names like test...).

share|improve this answer
    
I never thought there was problem in using the point-and-click in Management Studio. It seemed to work fine in previous versions... I'll try the command instead. –  Danielle Paquette-Harvey Jun 20 '13 at 15:53
    
I just don't think the UI contains all of the possible permutations that are available in the RESTORE command - including the case where you're restoring a database with a different name than the backup and when both databases still exist. –  Aaron Bertrand Jun 20 '13 at 15:55
    
Thanks a lot! I won't be relying on the UI anymore :-) –  Danielle Paquette-Harvey Jun 20 '13 at 15:56
add comment

1) Use WITH REPLACE while using the RESTORE command.

2) DROP the older database which is conflicting and restore again using RESTORE command.

There is no problem with the SQL Server version. As Aaron pointed out, I am also able to restore the database from 2008 to 2012 and same versions as well.

share|improve this answer
add comment

You're restoring the wrong database. Don't think of it as "restoring test2 with a backup from test", think "restore my backup of test but rename it as test2". You could choose the restore task from test and put test2 in the "To database:" field.

As Aaron mentions, learn the script rather than relying on the wizard--it's clearer as to what's going where.

share|improve this answer
add comment

You can still do this but it requires an extra step.

Open the restore dialog, make your usual selections per 2008 habits. If original database is DB then chose the DB.bak device for Source and change Destination name to DBTest. Then under Select A Page (upper left corner) you where you see General / Files / Options - choose Files. Look in the grid. You will see columns for Original File Name and Restore As File Name. Manually widen the latter and type in the new name (if original name of database is DB and you want to create DBTest then change ....\DB.mdf to ...\DBTest.mdf, etc.)

This will accept your .bak file for DB as the source and DBTest as the destination. You may also need to go to the Options page and choose With Replace. I need to for other reasons, so I cannot test it without this option.

share|improve this answer
add comment

The correct answer, as pointed out, is to use WITH REPLACE option.

However, you can get this error message despite this clause if you try to restore from a differential backup.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.