☰

使用覆盖 SQL Server 恢复数据库

tag-icon • tag-icon sql-server
使用覆盖 SQL Server 恢复数据库

这是一个脚本，它尝试从前一天获取每日备份并将其恢复到报告数据库。我的问题是如何将其设置为覆盖文件，这样我就不必指定文件名了？当我运行此脚本时，我遇到了一系列错误，如下所示。

----Restore Database
ALTER DATABASE ReportingDB SET SINGLE_USER
GO
DECLARE @filename VARCHAR(1000)
select @filename = 'F:\DailyBackup\LiveDB_backup_' + cast(datepart(yyyy, getdate()) as varchar(4)) + '' + substring(cast( 100 + datepart(mm, getdate()) as char(3)), 2, 2) + substring(cast( 100 + datepart(day, getdate()) as char(3)), 2, 2) + '0000.bak'
select @filename
RESTORE DATABASE ReportingDB FROM DISK = @filename WITH REPLACE
GO
ALTER DATABASE ReportingDB SET MULTI_USER
GO


(1 row(s) affected)
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "D:\DB\LiveDB.mdf" failed with the operating system error 21(error not found).
Msg 3156, Level 16, State 3, Line 4
File 'LiveDB' cannot be restored to 'D:\DB\LiveDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "D:\DB\LiveDB_log.LDF" failed with the operating system error 21(error not found).
Msg 3156, Level 16, State 3, Line 4
File 'LiveDB_log' cannot be restored to 'D:\DB\LiveDB_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

当我尝试移动时：

将“LiveDB”移动到“F:\ReportingDB\ReportingDB.mdf”，

将“LiveDB_log”移动到“F:\ReportingDB\ReportingDB_log.ldf”

(1 row(s) affected)
Msg 1834, Level 16, State 1, Line 4
The file 'F:\ReportingDB\ReportingDB.mdf' cannot be overwritten.  It is being used by database 'ReportingDB'.
Msg 3156, Level 16, State 4, Line 4
File 'LiveDB' cannot be restored to 'F:\ReportingDB\ReportingDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 4
The file 'F:\ReportingDB\ReportingDB_log.ldf' cannot be overwritten.  It is being used by database 'ReportingDB'.
Msg 3156, Level 16, State 4, Line 4
File 'LiveDB_log' cannot be restored to 'F:\ReportingDB\Reporting_log.ldf'.     Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

答案1

看起来您的原始备份是从位于的数据库获取的，'D:\DB\LiveDB.mdf'而您现在正尝试恢复位于的数据库'F:\ReportingDB\ReportingDB.mdf'，因此您需要结合使用 MOVE 和 REPLACE 选项。

RESTORE DATABASE ReportingDB 
    FROM DISK = @filename WITH REPLACE, 
    MOVE 'LiveDB' TO 'F:\ReportingDB\ReportingDB.mdf',
    MOVE 'LiveDB_log' TO 'F:\ReportingDB\ReportingDB_log.ldf'

答案2

你需要REPLACE选择

RESTORE DATABASE ReportingDB FROM DISK = @filename, REPLACE

看文档更多细节

答案3

消息 1834，级别 16，状态 1，第 4 行 无法覆盖文件“F:\ReportingDB\ReportingDB.mdf”。它正在被数据库“ReportingDB”使用。

上述错误可能是由于您尝试恢复时数据库正在使用中。这将阻止数据库恢复，因此请使用以下命令

更改数据库数据库名称设置 SINGLE_USER 并立即回滚

恢复数据库

请注意，如果您正在运行计划作业，则这应该是一步到位的。

这将强制所有其他用户断开连接（因此，如果正在运行他们的报告，则会失败）

您可能还需要包括以下内容

更改数据库数据库名称设置多用户

尽管恢复应该将其设置为与备份数据库相同，但随后允许多个连接。

相关内容