使用重写sql server还原数据库

Question

下面是一个脚本，它尝试从前一天开始进行每日备份，并通过报告数据库恢复备份。我的问题是如何设置它来覆盖文件，这样我就不必指定文件名了？当我运行这个程序时，我会遇到一系列错误，如下所示。

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

Answer 1

您的原始备份似乎是从位于'D:\DB\LiveDB.mdf'的数据库中提取的，您现在正试图通过位于'F:\ReportingDB\ReportingDB.mdf'的数据库进行恢复，因此您将需要移动选项和替换选项的组合。

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

Answer 2

您需要REPLACE选项

RESTORE DATABASE ReportingDB FROM DISK = @filename, REPLACE

有关更多细节，请参见文献资料

Answer 3

Msg 1834，16级，状态1，第4行文件'F:\ReportingDB\ReportingDB.mdf‘不能被覆盖。数据库'ReportingDB‘正在使用它。

以上错误可能是由于数据库在尝试还原时正在使用。这将防止数据库按此方式还原，请使用以下命令

将SINGLE_USER设置为立即回滚

还原数据库

请注意，如果您正在运行计划作业，这应该是在一个步骤中完成的。

这将迫使所有其他用户断开连接(因此，如果他们正在运行，他们的报告就会失败)

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

集MULTI_USER

为了允许随后的多个连接，尽管还原应该将其设置为与备份的数据库相同。