SQL 如何在同一台服务器上备份和恢复数据库作为副本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1360529/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you backup and restore a database as a copy on the same server?
提问by RSolberg
I have a SQL2005 Express database that I would like to create a copy of on the same instance. How do you go about doing this with a script?
我有一个 SQL2005 Express 数据库，我想在同一个实例上创建一个副本。你如何用脚本来做到这一点？
I already have a script for generating the backup, but the restore is failing...
我已经有一个用于生成备份的脚本，但是还原失败了...
THE ERROR:
错误：
Msg 3234, Level 16, State 2, Line 2 Logical file 'MyDB_data' is not part of database 'MyDB_Test'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
消息 3234，级别 16，状态 2，第 2 行 逻辑文件“MyDB_data”不是数据库“MyDB_Test”的一部分。使用 RESTORE FILELISTONLY 列出逻辑文件名。
消息 3013，级别 16，状态 1，第 2 行 RESTORE DATABASE 异常终止。
THE RESOLUTION:
决议：
RESTORE DATABASE [MyDB_Test]
FROM DISK = 'C:\temp\SQL\MyDB.bak'
WITH
MOVE 'MyDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDB_Test.mdf'
, MOVE 'MyDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDB_Test_log.ldf'
, REPLACE;
THE REASON:
I did not identify the logical path correctly in my first attempt.
原因：
我第一次尝试时没有正确识别逻辑路径。
回答by Nick Kavadias
RESTORE FILELISTONLYis an informational command and is not required to perform a restore. A user can use this to figure out what the logical names are for the data files, that can be used with the MOVEcommands to restore the database to a new location.
RESTORE FILELISTONLY是一条信息性命令，不需要执行还原。用户可以使用它来确定数据文件的逻辑名称是什么，可以与MOVE命令一起使用将数据库恢复到新位置。
As suggested by the error message you need to use RESTORE FILELISTONLYto see what the logical names for the database are. Your restore command has these wrong.
正如错误消息所建议的那样，您需要使用它RESTORE FILELISTONLY来查看数据库的逻辑名称是什么。你的恢复命令有这些错误。
Here is a working example of what you need to do:
以下是您需要做的工作示例：
--backup the database
backup database test1 to disk='c:\test1_full.bak'
-- use the filelistonly command to work out what the logical names
-- are to use in the MOVE commands. the logical name needs to
-- stay the same, the physical name can change
restore filelistonly from disk='c:\test1_full.bak'
--------------------------------------------------
| LogicalName | PhysicalName |
--------------------------------------------------
| test1 | C:\mssql\data\test1.mdf |
| test1_log | C:\mssql\data\test1_log.ldf |
-------------------------------------------------
restore database test2 from disk='c:\test1_full.bak'
with move 'test1' to 'C:\mssql\data\test2.mdf',
move 'test1_log' to 'C:\mssql\data\test2.ldf'
回答by Derek Swingley
回答by T.Todua
Here are some alternatives:
以下是一些替代方案：
Database restore (from .BAK) softwares::
数据库恢复（从 .BAK）软件：
2) Apex SQL 还原
