[SQLSERVER] [RESTORE] 逐步恢复日志备份并使用只读模式查看
以下代码中,所有红字均需要替换为实际场景代码。执行:
1 USE master 2 GO 3 4 declare @dest_db NVARCHAR(max) = N'DbName' 5 declare @standby_file NVARCHAR(max) = N'C:\可写路径\Standby.standby' 6 declare @logical_datafile NVARCHAR(max) = 'Db' 7 declare @logical_logfile NVARCHAR(max) = 'Db_log' 8 declare @dest_datafile NVARCHAR(max) = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Db.mdf' 9 declare @dest_logfile NVARCHAR(max) = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Db_log.ldf' 10 11 --Restore full backup 12 RESTORE DATABASE @dest_db 13 FROM DISK = N'\\全量备份路径.bak' 14 WITH REPLACE, STANDBY = @standby_file, move @logical_datafile to @dest_datafile, move @logical_logfile to @dest_logfile 15 16 --Restore optional differential backup 17 RESTORE DATABASE @dest_db 18 FROM DISK = N'\\增量备份路径.dif' 19 WITH STANDBY = @standby_file, move @logical_datafile to @dest_datafile, move @logical_logfile to @dest_logfile 20 21 --Restore optional transactional log backup 22 RESTORE DATABASE @dest_db FROM DISK = '\\日志备份路径.trn' WITH STANDBY = @standby_file, move @logical_datafile to @dest_datafile, move @logical_logfile to @dest_logfile
此时数据库处于 Standby / Read-Only 模式
————————————————————
最后若要使其上线,执行:
restore database DbName with recovery go