如何在 SQL Server 中还原到不同的数据库? [英] How to restore to a different database in SQL Server?

问题描述

我有一周前 Database1 的备份.备份每周在调度程序中完成，我得到一个 .bak 文件.现在我想处理一些数据，所以我需要将它恢复到不同的数据库 - Database2.

I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler and I get a .bak file. Now I want to fiddle with some data so I need to restore it to a different database - Database2.

我见过这个问题:还原 SQL Server在同一台电脑中使用不同名称的数据库，建议的步骤是重命名原始数据库，但我不在该选项中，因为我在生产服务器中，我无法真正做到.

I have seen this question: Restore SQL Server database in same pc with different name and the recommended step is to rename the original db, but I am out of that option as I am in the production server and I cant really do it.

是否有其他方法可以将其恢复到 Database2，或者至少，我如何浏览该 .bak 文件的数据?

Is there any other way of restoring it to Database2, or atleast, how do I browse through the data of that .bak file?

谢谢.

ps:上面链接中的第二个答案看起来很有希望，但它不断以错误终止:

ps: the second answer from the above link looked promising but it keeps terminating with error:

Restore Filelist 异常终止

Restore Filelist is terminating abnormally

推荐答案

您可以创建一个新的数据库，然后使用还原向导"；启用覆盖选项或:

You can create a new db then use the "Restore Wizard" enabling the Overwrite option or:

查看备份文件的内容:

RESTORE FILELISTONLY FROM DISK='c:your.bak'

注意 .mdf & 的逻辑名称.ldf 从结果，然后:

note the logical names of the .mdf & .ldf from the results, then:

RESTORE DATABASE MyTempCopy FROM DISK='c:your.bak'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:MyTempCopy_log.ldf'

这将使用 your.bak 的内容创建数据库 MyTempCopy.

This will create the database MyTempCopy with the contents of your.bak.

(不要创建 MyTempCopy，它是在恢复过程中创建的)

(Don't create the MyTempCopy, it's created during the restore)

示例(将名为creditline"的数据库备份恢复到MyTempCopy"):

RESTORE FILELISTONLY FROM DISK='e:mssqlackupcreditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:mssqlackupcreditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:mssqlMyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:mssqlMyTempCopy_log.ldf'

>RESTORE DATABASE successfully processed 186 pages in 0.010 seconds (144.970 MB/sec).

这篇关于如何在 SQL Server 中还原到不同的数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！