Error 3154 while restoring a backup using WITH REPLACE

Question

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!

Answer 1

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...).

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

I just want to point out that you can get the error in the question even when using WITH REPLACE, if you try to restore from a differential backup (without the full one).

Answer 6

Hope this will work.

1. Once you set the path to restore the file.
2. Click "Options" on the left hand side.
3. Tick the Check box - "Close existing connections to destination database".
4. Click OK.

Answer 7

Please stop your running server and then restore db, it will help you out. :)