简单实现Standby Sql Server数据库

  • 来源: fengyu.china.com 作者: 若水   2008-04-01/14:29
  • 一、为什么要备份数据库?

    在现实IT世界里,我们使用的服务器硬件可能因为使用时间过长,而发生故障;Windows系列服务器有可能蓝屏或者感染病毒;SQL Server数据库也可能因为误操作或Bug而停止运行。

    如何有效备份SQL Server数据库,避免故障真正发生时长时间的宕机,是每个系统管理员必须面对的任务。

    二、简单实现Standby Sql Server 数据库的原理

    我这里介绍一种不需要多大硬件投入(只需一台专用或兼用备份服务器)的Standby SQL Server的简单配置和使用方法。

    数据库完全备份和日志备份文件通过Msdos下xcopy命令从工作环境复制到备份环境(比在SQL Server里设置日志转移方法要简单得多),备份环境再根据xcopy过来的备份文件设定作业(执行一些存储过程)来完成自动恢复操作。

    如果意外发生时,这样的备份体系当然还需要人为地来干预和恢复(如改变备份机器的IP地址和主机名或更改应用程序的连接数据库参数等),会丢失一些数据也在所难免。

    下面是我的测试环境Standby SQL Server备份体系图:

    三、备份和恢复案例介绍

    首先我们要了解系统所能承受的最长宕机时间是多少(假如是1小时),能承受的数据丢失最多是多少(假如是30分钟),用它来定下备份和恢复的目标:

    工作环境下的某一个SQL Server数据库(假如是db_test)必须设置成完全故障还原模式;

    然后在数据库维护计划里设定每天凌晨四点做一次完全数据库备份(每天从0:00开始,每20分钟做一次数据库日志文件的备份,直到23:59分);

    备份目录下只保留最近一天内的完全备份和日志备份文件;并把此目录共享。

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

    xcopy \\192.168.0.1\db_test_backup\ F:\backup_data\db_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:\backup_data\db_test', 'E:\sqlserver_data\db_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),#p#分页标题#e#
    @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


    过程p_delete_db_test_backup的源代码:

    if exists (
    select *
    from dbo.sysobjects
    where id = object_id(N'[dbo].[p_delete_db_test_backup]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[p_delete_db_test_backup]
    GO

    create PROCEDURE dbo.p_delete_db_test_backup
    AS
    DECLARE
    @year1 varchar(4),
    @month1 varchar(2),
    @day1 varchar(2),
    @sqlstr varchar(2000)
    begin

    --Get year & month &day fromat of the day before yesterday
    SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),1,4)#p#分页标题#e#
    SET @month1 =substring(convert(varchar,datepart(mm,getdate()-2)),1,2)
    SET @day1=substring(convert(varchar,datepart(dd,getdate()-2)),1,2)
    if len(@month1)<2 set @month1 = '0' + @month1
    if len(@day1)<2 set @day1 = '0' + @day1

    set @sqlstr='del F:\backup_data\db_test\db_test_db_'+@year1+@month1+@day1+'*.bak'

    select @sqlstr

    exec master..xp_cmdshell @sqlstr

    set @sqlstr='del F:\backup_data\db_test\db_test_tlog_'+@year1+@month1+@day1+'*.trn'

    select @sqlstr

    exec master..xp_cmdshell @sqlstr

    end

    四、结束语

    这里介绍的简单实现Standby Sql Server数据库方法在我的工作环境也是运行良好的。

    它没有主从服务器之间明显的依赖关系,没有复杂的配置,只要我们定好备份和恢复时间计划表,就可以简单实现实时备份数据库的目的了。


    评论 {{userinfo.comments}}

    {{money}}

    {{question.question}}

    A {{question.A}}
    B {{question.B}}
    C {{question.C}}
    D {{question.D}}
    提交

    驱动号 更多