SQL SERVER恢复数据错误解决：The backup set holds a backup of a database other than the existing

来源：互联网发布：大数据如何影响制造业编辑：程序博客网时间：2024/06/06 15:38

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];GOCREATE DATABASE test;GOCREATE DATABASE test2;GOBACKUP DATABASE test TO DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION;GORESTORE 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 masterRESTORE FILELISTONLY   FROM DISK = 'g:\back.Bak' Go--**********************************/*利用bak恢复数据库，强制还原(REPLACE)STATS = 10 每完成10%显示一条记录DBTest和DBTest_log是上面g:\back.Bak里的逻辑文件*/USE masterRESTORE 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, REPLACEGO-------------------------------------/*备份数据DB 到.bak文件。然后利用此bak文件恢复一个新的数据库DBTest。*/USE masterBACKUP 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.修改【将数据库文件还原为】区域里的【还原为】的位置，和要恢复的数据库的实际位置保持一致

0 0