在SQL Server 2005中还原数据库 [英] Restore database in SQL Server 2005

问题描述

BACKUP DATABASE [MPRM] TO  DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 
WITH NOFORMAT, NOINIT,  NAME = N'MPRM-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

备份过程正常进行，我在shared文件夹(D:\shared\)中得到了一个名为MPRM_15_5_10.BAK的文件.这是从另一台计算机创建的备份.

The backup process worked, and I got a file named MPRM_15_5_10.BAK in my shared folder (D:\shared\). This is a backup created from another machine.

当我尝试还原备份时，请使用以下脚本

When I try to restore the backup, using the following script

RESTORE DATABASE [MPRM] 
FROM DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 
WITH FILE = 1,  NOUNLOAD, STATS = 10

我遇到以下错误

消息5133，第16级，状态1，第1行
对文件"E:\ DATABASES \ MPRM.mdf"的目录查找失败，出现操作系统错误2(系统找不到指定的文件.).
信息3156，第16级，状态3，第1行
无法将文件"MPRM"还原到"E:\ DATABASES \ MPRM.mdf".使用WITH MOVE标识文件的有效位置.
信息5133，第16级，状态1，第1行
对文件"E:\ DATABASES \ MPRM_log.ldf"的目录查找失败，发生操作系统错误2(系统找不到指定的文件.).
信息3156，第16级，状态3，第1行
无法将文件"MPRM_log"还原到"E:\ DATABASES \ MPRM_log.ldf".使用WITH MOVE标识文件的有效位置.
消息3119，第16级，状态1，第1行
在计划RESTORE语句时发现了问题.以前的消息提供了详细信息.
消息3013，第16级，状态1，第1行
RESTORE DATABASE异常终止.

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\DATABASES\MPRM.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'MPRM' cannot be restored to 'E:\DATABASES\MPRM.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\DATABASES\MPRM_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'MPRM_log' cannot be restored to 'E:\DATABASES\MPRM_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

为什么系统询问有关*.mdf, *.ldf文件的信息?它与备份"选项有关，而不是与还原脚本"有关吗?

Why the system asks about *.mdf, *.ldf files ? Is it anything related with Backup option rather than Restore script ?

我用Windows Authentication

推荐答案

如果目标服务器没有.bak，则在恢复.bak时需要指定存储物理文件的位置.与原始源服务器相同的磁盘/目录布局.备份文件包含逻辑SQL Server文件以及源服务器上的原始位置(.mdf和.ldf所在的完整物理路径).

You need to specify where to store the physical files when you restore a .bak if your target server doesn't have the same disk/directory layout as the original source server. The backup file contains the logical SQL Server files along with the original location on the source server (the full physical path where the .mdf and .ldf where located).

因此，您需要使用以下内容:

So you need to use something like this:

RESTORE DATABASE [MPRM] 
FROM DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 
WITH FILE = 1,  
MOVE N'MPRM' TO N'D:\MSSQL\Data\MPRM.mdf',  
MOVE N'MPRM_Log' TO N'D:\MSSQL\Data\MPRM_Log.ldf',  
NOUNLOAD, REPLACE,  
STATS = 10

此处命令:

MOVE N'MPRM' TO N'D:\MSSQL\Data\MPRM.mdf',  

指定称为MPRM的逻辑文件(在创建SQL Server数据库时未指定其他任何内容时的默认设置)，应在还原期间移动到物理位置D:\MSSQL\Data\MPRM.mdf (根据需要对此进行修改)

specifies that the logical file called MPRM (that's the default when you didn't specify anything else when creating your SQL Server database) should be moved during restore to the physical location D:\MSSQL\Data\MPRM.mdf (adapt this as needed)

要仅查看备份文件中包含的内容，可以在此处使用以下命令:

To just see what is contained inside a backup file, you can use this command here:

RESTORE FILELISTONLY
FROM DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 

这将向您显示备份中的所有逻辑文件，以及从中备份原始逻辑文件的原始物理文件(在运行备份命令的源服务器上).

This will show you all the logical files inside your backup, along with their original physical file that they were backed up from (on the source server, where you ran the backup command).

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