主服务器上作业
DECLARE @strPath NVARCHAR(200)
DECLARE @dbname NVARCHAR(200)
DECLARE @sql NVARCHAR(300)
select @dbname='selectBookStation'
set @strPath = replace(left(convert(NVARCHAR(19),getdate(),120),10),'-','')
set @strPath = 'D:\baoku\'+@dbname+'_'+@strPath +'.trn'
select @sql='BACKUP log '+@dbname+' TO DISK = '+''''+@strPath+''''+' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
exec sp_executesql @sql
----------------------------------------------------
备机上作业
备机恢复数据库
restore database selectBookStation
from disk='G:\log\selectBookStation.bak'
with move 'selectBookStation_Data' to 'F:\sqldata\selectBookStation.mdf',
move 'selectBookStation_Log' to 'E:\databaselog\selectBookStation_log.ldf',
STANDBY='F:\sqldata\undo\ROLLBACK_UNDO_selectBookStation.BAK',replace;
DECLARE @strPath NVARCHAR(200)
DECLARE @dbname NVARCHAR(200)
DECLARE @sql NVARCHAR(300)
select @dbname='selectBookStation'
set @strPath = replace(left(convert(NVARCHAR(19),getdate(),120),10),'-','')
set @strPath = 'D:\baoku\'+@dbname+'_'+@strPath +'.trn'
select @sql='BACKUP log '+@dbname+' TO DISK = '+''''+@strPath+''''+' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10'
exec sp_executesql @sql
----------------------------------------------------
备机上作业
备机恢复数据库
restore database selectBookStation
from disk='G:\log\selectBookStation.bak'
with move 'selectBookStation_Data' to 'F:\sqldata\selectBookStation.mdf',
move 'selectBookStation_Log' to 'E:\databaselog\selectBookStation_log.ldf',
STANDBY='F:\sqldata\undo\ROLLBACK_UNDO_selectBookStation.BAK',replace;
restore作业
DECLARE @strPath NVARCHAR(200)
DECLARE @dbname NVARCHAR(200)
DECLARE @sql NVARCHAR(300)
DECLARE @str NVARCHAR(300)
select @dbname='selectBookStation'
select @str=' with move '+''''+'selectBookStation_Data'+''''+' to '+''''+'F:\sqldata\selectBookStation.mdf'+''''+', move '+''''+'selectBookStation_Log'+''''+' to '+''''+'E:\databaselog\selectBookStation_log.ldf'+''''+',STANDBY='+''''+'F:\sqldata\undo\ROLLBACK_UNDO_selectBookStation.BAK;'+''''
set @strPath = replace(left(convert(NVARCHAR(19),getdate(),120),10),'-','')
set @strPath = 'G:\log\'+@dbname+'_'+@strPath +'.trn'
select @sql='restore log '+@dbname+' from disk = '+''''+@strPath+''''+@str
exec sp_executesql @sql
DECLARE @dbname NVARCHAR(200)
DECLARE @sql NVARCHAR(300)
DECLARE @str NVARCHAR(300)
select @dbname='selectBookStation'
select @str=' with move '+''''+'selectBookStation_Data'+''''+' to '+''''+'F:\sqldata\selectBookStation.mdf'+''''+', move '+''''+'selectBookStation_Log'+''''+' to '+''''+'E:\databaselog\selectBookStation_log.ldf'+''''+',STANDBY='+''''+'F:\sqldata\undo\ROLLBACK_UNDO_selectBookStation.BAK;'+''''
set @strPath = replace(left(convert(NVARCHAR(19),getdate(),120),10),'-','')
set @strPath = 'G:\log\'+@dbname+'_'+@strPath +'.trn'
select @sql='restore log '+@dbname+' from disk = '+''''+@strPath+''''+@str
exec sp_executesql @sql