简单实现Standby Sql Server数据库

核心提示： 备份环境下的服务器在[控制面板]->[任务计划]里添加一个每天0:05分开始，每20分钟执行一次的xcopy局域网上备份目录下最新文件的任务，简单实现Standby Sql Server数据库(2)，直到23:59分，xcopy \192.168.0.１db_test_backup

备份环境下的服务器在[控制面板]->[任务计划]里添加一个每天0:05分开始，每20分钟执行一次的xcopy局域网上备份目录下最新文件的任务，直到23:59分。

xcopy \192.168.0.１db_test_backup F:ackup_datadb_test /c /y /d /s

备份服务器上SQL Server根据复制过来的备份文件，也每天0:10分开始，每20分钟执行一次由旧到新，逐一恢复数据库的作业（调用我改写的过程sp_RestoreDir实现）；

另外还有一个删除备份服务器两天前备份文件的作业（调用我写的过程p_delete_db_test_backup实现），避免备份硬盘扇区被装满。

过程sp_RestoreDir的源代码：

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_RestoreDir]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RestoreDir]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/***************************************************************************************/
-- 目　　的: 根据某个目录下某个数据库的备份文件(*.trn或*.bak都可以)还原数据库.
--　　　　　 配合xcopy命令，可以在另一台备份机器上实现standby SQL Server数据库.
--
-- 输入参数: @restoreFromDir - 存放*.trn或*.bak数据库备份文件的目录
--　　　　 @restoreToDataDir - 数据库数据文件将要还原的目录
--　　　　 @restoreToLogDir - 数据库日志文件将要还原的目录, 如果为空,
--　　　　 日志文件和数据文件目录相同
--
-- Written By:　　Chris Gallelli -- 8/22/2003
-- Modified By:　 Bruce Canaday -- 11/04/2003
-- 　　　　　http://www.sqlservercentral.com/scripts/contributions/962.asp
-- Modified By:　 maggiefengyu@tom.com --- 02/23/2005
--
-- 调用举例: exec sp_RestoreDir 'F:ackup_datadb_test', 'E:sqlserver_datadb_test'
/***************************************************************************************/
CREATE　 proc sp_RestoreDir
　　　 @restoreFromDir varchar(255),
　　　 @restoreToDataDir varchar(255)= null,
　　　 @restoreToLogDir varchar(255) = null
as
--If a directory for the Log file is not supplied then use the data directory
If @restoreToLogDir is null
　　set @restoreToLogDir = @restoreToDataDir
set nocount on
declare @filename　　　　 varchar(40),
　　@cmd　　　　　　　varchar(500),
　　　　@DataName　　　　 varchar (255),
　　@LogName　　　　　varchar (255),
　　　　@LogicalName　　　varchar(255),
　　@PhysicalName　　 varchar(255),
　　@Type　　　　　　 varchar(20),
　　@FileGroupName　　varchar(255),
　　@Size　　　　　　 varchar(20),
　　@MaxSize　　　　　varchar(20),
　　@restoreToDir　　 varchar(255),
　　@DBName　　　　　 varchar(255),
　　　　@PhysicalFileName varchar(255),
　　　　@i_exist　　　int
create table #dirList (id [int] IDENTITY (1, 1) NOT NULL , filename varchar(100))
create table #filelist (LogicalName varchar(255), PhysicalName varchar(255),
Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20) )
--Get the list of database backups that are in the restoreFromDir directory order by date desc
　 select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'
set @i_exist=0
insert into #dirList(filename) exec master..xp_cmdshell @cmd　
-- 找到备份目录下次新的2个文件名, 不处理最新的, 避免最新备份好的物理文件没有完全复制成功
select filename from #dirList where id>1 and id<8 order by id desc
begin　
　　 declare BakFile_csr cursor for
　　　 select filename from #dirList where id>1 and id<8 order by id desc
end
open BakFile_csr
fetch BakFile_csr into @filename
while @@fetch_status = 0
　 begin
--　判断恢复日志表restore_log存在否，不存在则创建表
　 　　if not exists (
　 　　　 　　select *
　 　　　 　　from dbo.sysobjects
　 　　　 　　where id = object_id('restore_log')
　 　　　 　　and OBJECTPROPERTY(id, N'IsUserTable') = 1)
　　begin
　　　 create table restore_log (filename varchar(128),dt datetime default getdate())
　　end
--　判断此备份文件有无在恢复日志表里记录过?
　　　　select @i_exist=count(0) from restore_log where filename=@filename
　　　　if @i_exist=0
　　　　begin
-- 根据备份目录下的物理文件名, 找到对应的逻辑名等参数
　　　　select @cmd = "RESTORE FILELISTONLY FROM disk = '"
　　　　+ @restoreFromDir + "" + @filename + "'"
　　　 　　insert #filelist exec ( @cmd )
　　　 　　
--　　 　　select * from #filelist
　　　　if right(@filename,3)='TRN'
　　　　begin
　　　 　　select @dbName = left(@filename,datalength(@filename)
　　　 　　- patindex('%_golt_%',reverse(@filename))-5)
　　　 　　select @cmd = "RESTORE Log " + @dbName +
　　　　" FROM DISK = '" + @restoreFromDir + "" + @filename +
　　　　"' WITH STANDBY='"+@restoreToDataDir+"UNDO_"+@filename+".DAT ',"
　　　 　　print ''
　　　 　　print '--RESTORING Log　' + @dbName
　　　　end
　　　　if right(@filename,3)='BAK'
　　　　begin
　　　　　select @dbName = left(@filename,datalength(@filename)
　　　　　- patindex('%_bd_%',reverse(@filename))-3)
　　　 　　　select @cmd = "RESTORE DATABASE " + @dbName +
　　　　" FROM DISK = '" + @restoreFromDir + "" + @filename +
　　　　"' WITH NORECOVERY ,"
　　　 　　　print ''
　　　　　print '--RESTORING DATABASE ' + @dbName
　　　　end　　
-- 找到数据库逻辑和物理文件名称之间的对应关系
　　　 　　declare DataFileCursor cursor for　
　　　　select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
　　　　from #filelist
　　　 　　open DataFileCursor
　　　 　　fetch DataFileCursor into @LogicalName, @PhysicalName, @Type,
　　　 　　@FileGroupName, @Size, @MaxSize
　　　 　　while @@fetch_status = 0
　　　　　begin
　　　　　　　　 -- RESTORE with MOVE option
　　　　　　　 select @PhysicalFileName = reverse(substring(
　　　　　　　 reverse(rtrim(@PhysicalName)),1,patindex('%\%',
　　　　　　　 reverse(rtrim(@PhysicalName)))-1 ))
　　　　　　select @restoreToDir = @restoreToDataDir
　　　 　　　　select @cmd = @cmd +
　　　　　　　　　　" MOVE '" + @LogicalName + "' TO '" +
　　　 　　　　　　　 　　@restoreToDir + "" + @PhysicalFileName + "', "
　　　　　　　fetch DataFileCursor into @LogicalName, @PhysicalName,
　　　　　　　@Type, @FileGroupName, @Size, @MaxSize
　　　　　end　-- DataFileCursor loop
　　close DataFileCursor
　　　　deallocate DataFileCursor
　　　 　　select @cmd = @cmd + ' REPLACE'
　　 　　print @cmd 　　　　
　　 　　print ''
　　 　　
　　 　　select @cmd
　　 　　
　　　 　　EXEC (@cmd)
　　IF @@ERROR=0
　　BEGIN
-- 如果恢复成功,记恢复操作日志
　　　 delete from restore_log where dt<getdate()-2
　　　　　 insert into restore_log (filename) values (@filename)
　　　　END
　　　 　　truncate table #filelist
　　　 　　
　　　 end -- @i_exist=0
　　 fetch BakFile_csr into @filename
　 end -- BakFile_csr loop
close BakFile_csr
deallocate BakFile_csr
drop table #dirList
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO