如何在同一台服务器上备份和还原数据库作为副本？

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.

原因：我在第一次尝试时没有正确识别逻辑路径。

3 个解决方案

#1

RESTORE FILELISTONLY is 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 MOVE commands to restore the database to a new location.

RESTORE FILELISTONLY是一个信息命令，不需要执行还原。用户可以使用它来确定数据文件的逻辑名称，可以与MOVE命令一起使用以将数据库还原到新位置。

As suggested by the error message you need to use RESTORE FILELISTONLY to see what the logical names for the database are. Your restore command has these wrong.

根据错误消息的建议，您需要使用RESTORE FILELISTONLY来查看数据库的逻辑名称。您的restore命令有这些错误。

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'

#2

How to: Restore a Database to a New Location and Name (Transact-SQL)

如何：将数据库还原到新位置和名称（Transact-SQL）

#3

Here are some alternatives:

以下是一些替代方案：

Database restore (from .BAK) softwares::

数据库恢复（来自.BAK）软件::

1) SqlRestoreSetup

1）SqlRestoreSetup

2) Apex SQL Restore

2）Apex SQL Restore

#1