sql-server - 如何查询 SQL Server 备份文件

Question

我正在使用 SQL Server 2008 R2，我有一个备份文件 B:\backups\full_backup.bak 但我不知道这个文件有哪些备份或每个备份有哪些文件。如何编写查询以从此备份文件恢复数据库？

我试图恢复数据库，但它抛出一个错误提示

Msg 3156, Level 16, State 4, Line 1
File 'Application_Primary' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Application_Primary.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 3156, Level 16, State 4, Line 1
File 'Application_FTS' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Application_FTS.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 3156, Level 16, State 4, Line 1
File 'Application_Log' cannot be restored to 'L:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Application_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.

代码:

restore database Application_db
From disk = 'B:\backups\full_backup.bak'

Answer 1

在我看来，您正在尝试恢复一个备份，该备份具有来自另一台服务器的数据库备份，其中存储了一些数据文件(.mdf、ldf)的路径。

您需要使用 MOVE 子句恢复数据库。只是 RESTORE 命令会尝试将数据库还原到进行此备份的数据库路径。

查看备份文件中有哪些备份

USE master;
GO

RESTORE HEADERONLY 
FROM DISK = N'B:\backups\full_backup.bak'
GO

这将返回此备份文件保存的备份。您需要确定备份文件的位置。

检查备份文件中有哪些文件

USE master;
GO

RESTORE FILELISTONLY 
FROM DISK = N'B:\backups\full_backup.bak'
 WITH FILE = 1    --<-- Position of backup in the backup file 
GO

这将返回特定备份的文件。您将需要这些文件名用于恢复语句中的 MOVE 子句。

恢复数据库

USE master;
GO

RESTORE DATABASE [Application_DB]
 FROM DISK = N'B:\backups\full_backup.bak'
WITH FILE = 1             --<-- position of database backup in the backup file 
,MOVE N'Application_Primary' TO N'D:\<some valid path>\Application_Primary.mdf'
,MOVE N'Application_FTS'     TO N'D:\<some valid path>\Application_FTS.ndf'
,MOVE N'Application_Logy'    TO N'D:\<some valid path>\Application_Log.ldf'
,RECOVERY;

开始