如何在同一服务器上将数据库备份和恢复为副本？

发布于 2024-08-03 23:00:01 字数 699 浏览 5 评论 0原文

我有一个 SQL2005 Express 数据库，我想在同一实例上创建它的副本。您如何使用脚本来做到这一点？

我已经有一个用于生成备份的脚本，但恢复失败...

错误：

消息 3234，级别 16，状态 2，第 2 行逻辑文件“MyDB_data”不是一部分数据库“MyDB_Test”的。使用恢复 FILELISTONLY 列出逻辑文件名字。
消息 3013，16 级，状态 1，第 2 行 RESTORE DATABASE 正在终止异常。

解决方案：

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;

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

原文

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?

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏雨凉 2024-08-10 23:00:01

RESTORE FILELISTONLY 是一条信息性命令，不需要执行恢复。用户可以使用它来找出数据文件的逻辑名称，这些逻辑名称可以与 MOVE 命令一起使用以将数据库恢复到新位置。

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

这是您需要执行的操作的示例：

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

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.

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.

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'

回复收藏 0 原文