Error 3154 while restoring a backup using WITH REPLACE

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:

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

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

-
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!

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

StackExchange Database Administrators Q#44967, answer score: 13