SQL SERVER恢復資料錯誤解決：The backup set holds a backup of a database other than the existing

http://dba.stackexchange.com/questions/44967/error-3154-while-restoring-a-backup-using-with-replace

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

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.

http://blog.sqlauthority.com/2013/11/23/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database-ssms/

http://blog.csdn.net/j2eevic/article/details/7408432

方法一：

--返回由備份集內包含的資料庫和日誌檔案列表組成的結果集。
--主要獲得邏輯檔名
USE master
RESTORE FILELISTONLY
   FROM DISK = 'g:\back.Bak' 
Go
--**********************************
/*
利用bak恢復資料庫，強制還原(REPLACE)
STATS = 10 每完成10%顯示一條記錄
DBTest和DBTest_log是上面g:\back.Bak裡的邏輯檔案
*/
USE master
RESTORE DATABASE DB 
   FROM DISK = 'g:\back.Bak'
   WITH MOVE 'DBTest' TO 'E:\Program Files\Microsoft SQL Server2005\Data\DB.mdf', 
   MOVE 'DBTest_log' TO 'E:\Program Files\Microsoft SQL Server2005\Data\DB_log.ldf',
STATS = 10, REPLACE
GO
-------------------------------------

/*
備份資料DB 到.bak檔案。然後利用此bak檔案恢復一個新的資料庫DBTest。
*/
USE master
BACKUP DATABASE DB 
  TO DISK = 'g:\DBBack0930.bak' 
RESTORE FILELISTONLY 
  FROM DISK = 'g:\DBBack0930.bak' 
RESTORE DATABASE DBTest 
  FROM DISK = 'g:\DBBack0930.bak' 
  WITH MOVE 'DBTest' TO 'E:\Program Files\Microsoft SQL Server2005\Data\DBTest.mdf', 
  MOVE 'DBTest_log' TO 'E:\Program Files\Microsoft SQL Server2005\Data\DBTest_log.ldf'
GO 
---******************************

方法二
需要注意兩點：
在【選項】介面裡
1.選擇“覆蓋現有資料庫”
2.修改【將資料庫檔案還原為】區域裡的【還原為】的位置，和要恢復的資料庫的實際位置保持一致