1、SQL Server自动化运维 - 备份(一)业务数据库

为了能够恢复数据，数据库运维基础就是备份，备份自动化也是运维自动化首要进行的。

笔者的备份自动化，通过配置表快速配置为前提，同时记录备份过程，尽可能的减少人工操作。首先将SQL Server备份按用途分：

1、 业务数据库备份--本文主要内容

SQL Server提供多种备份方式，为业务数据库备份选择何种备份策略，可根据各自的数据量和业务情况而定。

备份策略需求：

• 可恢复到备份之前的任意时间点，尽量减少数据丢失
• 单个备份数据库已达TB
• 考虑还原效率、不可追加过多的日志

备份策略制定：

• 完整备份：每周一执行一次
• 差异备份：每天执行一次，周一不执行
• 日志备份：每1小时一次
• 备份副本：保存多份备份副本；上传一份副本到远程磁盘dbbackup文件夹
• 备份归档：归档远程磁盘备份到dbbackuphistory文件夹，只保留当月最早完整备份和所有日志备份，删除差异备份
• 通过配置表控制是否备份，备份路径等；并记录备份信息，备份大小，开始时间，结束时间等

注：在执行完整或差异备份的同时，可同时执行日志备份。

2、系统数据库、配置数据库、高可用配置、权限、Job、SSIS等备份；以下简称为“灾备”--后续文章介绍

灾备的目的在于，发生重大服务器故障，可迅速通过灾备恢复原有配置，权限用户等等。

一、备份架构

基础备份架构如图所示：

方案一、将数据先备份到所在服务器的本地磁盘，再上传到远程磁盘柜(多份数据副本)，最后针对磁盘柜的备份进行归档处理。之所以先备份到本地，是考虑备份最好保存多份，以防止文件损坏。

方案二、若本地磁盘空间不足，或者io压力较大，可直接备份到远程。

二、配置表

指定两张配置表，BackupRestoreSetting为备份配置信息，备份路径，上传路径，是否备份，是否上传控制；BackupFileList记录备份信息，备份文件名称、大小、开始时间、结束时间等。

2.1、BackupRestoreSetting备份还原配置信息表

如下图所示，插入该数据库的备份还原配置信息，备份磁盘路径，上传路径，是否备份是否上传等信息。后续脚本封装将读取这些配置信息进行备份。

注：由于还原是以备份信息为基础，所以配置信息需要放在一起。此处暂时刻忽略还原内容。

--运维配置库，收集本地服务器所有自动化信息；包括dmv，扩展事件，计数器，备份，日常维护脚本封装等。
USE [Configdb]
GO
--备份还原配置表
CREATE TABLE [dbo].[BackupRestoreSetting](
    [Servername] [sysname] NOT NULL,--服务器名称
    [DBname] [sysname] NOT NULL,--数据库名称
    [V_DBname] [nvarchar](128) NULL,--还原数据库名称
    [Backup_drive] [char](1) NULL,--备份磁盘
    [Backup_path] [varchar](100) NULL,--备份路径
    [Restore_drive] [char](1) NULL,--还原磁盘
    [Restore_path] [varchar](100) NULL,--还原路径
    [Copy_path] [varchar](100) NULL,--上传路径
    [IsBackup] [char](1) NULL,--是否备份
    [IsCopy] [char](1) NULL,--是否上传
    [IsRestore] [char](1) NULL,--是否还原
    [RestoreStatus] [char](1) NULL,--还原状态
    [Priority] [smallint] NULL,--还原优先级
    [Net_Ip] [varchar](100) NULL,--远程磁盘柜IP
 CONSTRAINT [PK_BackupRestoreSetting] PRIMARY KEY CLUSTERED
(
    [Servername] ASC,
    [DBname] ASC
)
) ON [PRIMARY]

2.2、BackupFilelist备份信息记录表

记录备份大小，开始时间，结束时间，上传时间等等；以便统计和备份异常处理。

--备份信息记录表
CREATE TABLE [dbo].[BackupFilelist](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Servername] [varchar](50) NULL,--服务器名称
    [Dbname] [varchar](50) NULL,--数据库名称
    [Backupset] [varchar](4) NULL,--备份模式，完整FULL 差异DIFF 日志LOG
    [Filename] [varchar](100) NULL,--备份文件名称
    [Filesize] [varchar](20) NULL,--备份文件大小
    [Backup_start_time] [datetime] NULL,--备份开始时间
    [Backup_end_time] [datetime] NULL,--备份结束时间
    [Copy_end_time] [datetime] NULL,--上传开始时间
    [Copy_status] [char](1) NULL,--上传状态
    [ReCopyTimes] [int] NULL,--重复上传次数
    [Media_set_id] [int] NULL,--上传文件meidaid
    [Error_Msg] [varchar](max) NULL,--错误信息
    [Cmd] [varchar](max) NULL,--上传命令
 CONSTRAINT [PK_BackupFilelist] PRIMARY KEY CLUSTERED
(
    [id] ASC
)
)
GO

三、spx_Backup_DelFile删除本地备份

若备份存放在本地，需要定期删除本地备份，以防止本地磁盘不足；建议在执行完整FULL备份之前来删除本地备份，以保证可快速还原完整备份之后的任意时间点。

删除本地备份脚本spx_Backup_DelFile

USE [Configdb]
GO

/****************************** 功能描述：<本地备份文件删除>
*   创建者：<HuangCH〉
*   创建日期：<2015-12-03>
*   备注说明：<@Retain_weeks 保留周数，默认全部删除>
##########
Change Log
##########
Date                 Changer             Description
--------------------------------------------------
<2015-12-03>       <HuangCH>            <新建>
--------------------------------------------------
***************************/

CREATE PROCEDURE [dbo].[spx_Backup_DelFile]
          @servername [SYSNAME],
          @dbname     [SYSNAME],
          @Retain_weeks      INT=0
AS
  SET nocount on
  ----===============================
  DECLARE  @retcode INT
  DECLARE  @cmd VARCHAR(2000)
  DECLARE  @backup_drive CHAR(1)--本地备份磁盘
  DECLARE  @backup_path VARCHAR(200)--本地备份路径'

  SELECT @backup_drive = backup_drive,
         @backup_path = backup_path

  FROM   dbo.BackupRestoreSetting WITH (nolock)
  WHERE  servername=@servername and dbname = @dbname

  IF @Retain_weeks=0
  BEGIN
    --删除备份
      SET @cmd='del '+@backup_drive +':\'+@backup_path+'\'+ @dbname + '_??????????????_*_???.bak'
      EXEC master..Xp_cmdshell @cmd
    END
    ELSE IF @Retain_weeks>0
    BEGIN
       DECLARE @delday Datetime
       SET @delday=CONVERT(VARCHAR,DATEADD(WEEK,-@Retain_weeks,GETDATE()),23)
       WHILE @delday>=DATEADD(WEEK,-@Retain_weeks-3,GETDATE())
       BEGIN
         SET @delday=DATEADD(D,-1,@delday)
         SET @cmd='del '+@backup_drive +':\'+@backup_path+'\'+ @dbname + '_'+CONVERT(VARCHAR,@delday,112)+'*_*_???.bak'
         EXEC master..Xp_cmdshell @cmd
       END

    END

spx_Backup_DelFile

例如：删除本地sqlmonitor服务器下configdb备份；插入配置信息，传入@servername和@dbname 可删除某个数据库的本地备份

USE Configdb
GO
--插入配置信息
INSERT INTO [dbo].[BackupRestoreSetting](Servername,DBname,V_DBname,Backup_drive,Backup_path,Restore_drive,Restore_path,Copy_path,IsBackup,IsCopy)
VALUES('sqlmonitor','configdb','configdb','d','backup','d','sqldasta','m:\dbbackup\sqlmonitor','T','F')
GO
--完整备份：确认d:\backup目录是否存在
EXEC spx_Backup_DelFile @servername='sqlmonitor',@dbname='configdb',@Retain_weeks=0--默认为0,若1值则多保留1周

四、spx_Backup_DB备份数据库

USE [Configdb]
GO
GO
/****************************** 功能描述：<备份数据库>
*   创建者：<HuangCH〉
*   创建日期：<2015-12-03>
*   备注说明：<
1、备份类型只能是"FULL","DIFF","LOG"'
>
##########
Change Log
##########
Date                 Changer             Description
--------------------------------------------------
<2015-12-03>       <HuangCH>            <新建>

--------------------------------------------------
***************************/

ALTER proc [dbo].[spx_Backup_DB]
@servername [varchar](50),
@dbname [sysname],
@backupset [varchar](4)
as
----确定linkedserver是否正常
--DECLARE  @servername [VARCHAR](50),
--         @dbname     [SYSNAME],
--         @backupset  [VARCHAR](4)
/*确认参数信息是否正确*/
IF @backupset NOT IN ('full','log','diff')
  BEGIN
    PRINT '''' + Upper(@backupset) + ''' is not a valid value for argument @backupset.'
    RETURN -1000
  END
IF Db_id(@dbname) IS NULL
  BEGIN
    PRINT 'Database ''' + @dbname + ''' not found.'
    RETURN -2000
  END
IF NOT EXISTS(SELECT TOP 1 1 FROM dbo.BackupRestoreSetting  WHERE servername = @servername AND dbname = @dbname)
  BEGIN
    RAISERROR ('备份还原配置表信息不存在，请确认',16,1)
    RETURN -4000
  END
DECLARE  @version VARCHAR(4)--sql server版本
DECLARE  @Backup_dirve CHAR(1)--备份磁盘
DECLARE  @backup_path VARCHAR(1000)--备份路径
DECLARE  @copy_path VARCHAR(50)--上传路径
DECLARE  @IsCopy CHAR(1)--是否已经上传
DECLARE  @filename VARCHAR(2000)--备份文件名称
DECLARE  @retcode INT
DECLARE  @errorMessage VARCHAR(2000)
DECLARE  @netdevice CHAR(1)
DECLARE  @net_ip VARCHAR(20)
DECLARE  @id INT
DECLARE  @cmd VARCHAR(4000)
/*确定数据库版本*/
SET @version =CASE WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '9.0%' THEN ''
                      WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '10%' THEN ''
                      WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '11%' THEN ''
                      WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '12%' THEN ''
                      ELSE '' END 

SELECT @Backup_dirve = backup_drive,
       @backup_path = backup_path,
       @copy_path = copy_path,
       @IsCopy = IsCopy,
       @net_ip = net_ip
FROM   dbo.BackupRestoreSetting WITH (NOLOCK)
WHERE  servername = @servername
       AND dbname = @dbname

/*确定备份文件名称*/
SET @filename = @dbname + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),Getdate(),21),'-',''),' ',''),':','') + '_' + Upper(@backupset) + '_P' + @version + '.bak'

/*确定本地备份路径*/
SET @backup_path =  @Backup_dirve + ':\' + @backup_path + '\' + @filename
/*记录备份文件基本信息，和备份开始时间*/
INSERT INTO dbo.BackupFilelist
(
Servername,
Dbname,
Backupset,
Filename,
Filesize,
Backup_start_time,
Backup_end_time,
Copy_end_time,
Copy_status,
ReCopyTimes,
Media_set_id
)
VALUES     (@servername,
            @dbname,
            @backupset,
            @filename,
            NULL,
            Getdate(),
            NULL,
            NULL,
            NULL,
            NULL,
            NULL)
/*取记录id号*/
SET @ID=SCOPE_IDENTITY()
/*开始备份(log,full),覆盖现有备份集*/
BEGIN TRY
  IF @backupset='FULL'
  BEGIN
    SET @cmd='BACKUP DATABASE ['+@dbname+'] TO DISK='''+@backup_path+CASE WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '9.0%' THEN  ''' WITH INIT' ELSE ''' WITH INIT,COMPRESSION' END
  END
  ELSE IF @backupset='DIFF'
  BEGIN
    SET @cmd='BACKUP DATABASE ['+@dbname+'] TO DISK='''+@backup_path+CASE WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '9.0%' THEN  ''' WITH INIT,DIFFERENTIAL' ELSE ''' WITH INIT,COMPRESSION,DIFFERENTIAL' END
  END
  ELSE
  BEGIN
    SET @cmd='BACKUP LOG ['+@dbname+'] TO DISK='''+@backup_path+CASE WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '9.0%' THEN  ''' WITH INIT' ELSE ''' WITH INIT,COMPRESSION' END
  END
  --PRINT @cmd
  EXEC (@cmd)

END TRY
BEGIN CATCH
  SELECT @retcode = @@ERROR,  @errorMessage = Error_message()
  UPDATE dbo.BackupFilelist
  SET    Error_Msg=@errorMessage,
         Cmd=@cmd
  WHERE  id = @id
  RAISERROR (@errorMessage,16,1)
  RETURN @retcode
END CATCH

/*记录备份结束时间*/
UPDATE dbo.BackupFilelist
SET    backup_end_time = Getdate(),
       media_set_id = (SELECT   TOP 1  media_set_id  FROM   msdb.dbo.backupmediafamily WITH (nolock) ORDER BY media_set_id Desc),
       Cmd=@cmd
WHERE  id = @id
/*记录备份文件大小*/

DECLARE  @filesize   VARCHAR(20),
         @tempString VARCHAR(400)
SET @cmd = 'DIR ' + @backup_path
CREATE TABLE #tbl (line NVARCHAR(255))
INSERT INTO #tbl
EXEC master.dbo.Xp_cmdshell @cmd
SELECT @tempString = line
FROM   #tbl
WHERE  line LIKE '%' + @filename + '%'
IF (@tempString LIKE '%:__ _M %')  --like '%:__ AM %' OR @tempString like '%:__ PM %')
  SELECT @filesize = REPLACE(Rtrim(Ltrim(Substring(@tempString,Charindex(':',@tempString) + 6,
                                                   Charindex(@filename,@tempString) - Charindex(':',@tempString) - 6))),
                             ',','')
ELSE
  SELECT @filesize = REPLACE(Rtrim(Ltrim(Substring(@tempString,Charindex(':',@tempString) + 3,
                                                   Charindex(@filename,@tempString) - Charindex(':',@tempString) - 3))),
                             ',','')
UPDATE dbo.BackupFilelist
SET    filesize = @filesize
WHERE  id = @id

/*开始上传备份文件*/
DECLARE  @ReCopyTimes INT
SET @ReCopyTimes = 0
RECOPYFLAG:
IF @IsCopy = 'T'
  BEGIN
    /*映射@netdevice远程*/
    SELECT @netdevice = Substring(Ltrim(@copy_path),1,1)
    BEGIN TRY
      EXEC spx_NetUse @drive = @netdevice  output,
                      @ip = @net_ip
    END TRY
    BEGIN CATCH
      SELECT @errorMessage = Error_message()
      RAISERROR (@errorMessage,16,1)
      RETURN -5000
    END CATCH

    /*确认拷贝路径是否存在*/
     SELECT @cmd = 'DIR ' + @netdevice + Substring(@copy_path,2,Len(@copy_path))
     EXEC @retcode = master..Xp_cmdshell @cmd
     IF @@ERROR = 0 AND @retcode <> 0
       BEGIN
          SELECT @cmd = 'MD ' + @netdevice + Substring(@copy_path,2,Len(@copy_path))
          EXEC @retcode = master..Xp_cmdshell @cmd
       END

    SET @cmd = 'copy ' + @backup_path + ' ' + @netdevice + Substring(@copy_path,2,Len(@copy_path))

    EXEC @retcode = master..Xp_cmdshell @cmd
    IF @retcode <> 0
      BEGIN
        SET @ReCopyTimes = @ReCopyTimes + 1
        UPDATE dbo.BackupFilelist
        SET    recopytimes = @reCopyTimes,Copy_status='F'
        WHERE  id = @id
        PRINT '时间：' + CAST(Getdate() AS VARCHAR) + ' 重新copy次数：' + CAST(@reCopyTimes AS VARCHAR)  --输出重试次数
        /*删除@netdevice映射*/
        SELECT @cmd = 'net use ' + @netdevice + ':' + ' /delete /y'
        EXEC master..Xp_cmdshell @cmd
        IF (@reCopyTimes < 5) --最多重试五次
          GOTO recopyflag
        ELSE --大于5次即五次copy都失败，则报错
          BEGIN
            SELECT @errorMessage = ' 自定义错误：' + @backup_path + ' 无法拷贝到' + @netdevice + Substring(@copy_path,2,Len(@copy_path))
            RAISERROR (@ErrorMessage,16,1)
            RETURN @retcode
          END
      END

    /*记录上传时间*/
    UPDATE dbo.BackupFilelist
    SET    Copy_end_time = Getdate(),Copy_status='T',ReCopyTimes=@ReCopyTimes
    WHERE  id = @id
 END
/*删除@netdevice映射*/
SELECT @cmd = 'net use ' + @netdevice + ':' + ' /delete  /y'
EXEC master..Xp_cmdshell @cmd

RETURN 0

spx_Backup_DB

以上备份存储过程可执行三种类型备份FULL,LOG,DIFF；

1、在执行此存储过程之前，需要确认以上两个配置表是否已经建立。

2、存储过程内部调用spx_netuse；进行远程共享映射，具体原理如下章节（4.3、上传远程磁盘柜）

例如：添加sqlmonitor下configdb的备份；只备份到本地，不上传远程

USE Configdb
GO
--插入配置信息
INSERT INTO [dbo].[BackupRestoreSetting](Servername,DBname,V_DBname,Backup_drive,Backup_path,Restore_drive,Restore_path,Copy_path,IsBackup,IsCopy)
VALUES('sqlmonitor','configdb','configdb','d','backup','d','sqldasta','m:\dbbackup\sqlmonitor','T','F')
GO
--完整备份：确认d:\backup目录是否存在
EXEC spx_Backup_DB @servername='sqlmonitor',@dbname='configdb',@backupset='FULL'
--差异备份
EXEC spx_Backup_DB @servername='sqlmonitor',@dbname='configdb',@backupset='DIFF'
--日志备份
EXEC spx_Backup_DB @servername='sqlmonitor',@dbname='configdb',@backupset='LOG'

4.1、备份文件命名格式

数据库名称_备份时间_备份模式_数据库版本.BAK

例子：Configdb_20161009182755_LOG_P12.BAK

4.2、备份脚本解释

BACKUP DATABASE ['+@dbname+'] TO DISK ='''+@backup_path+ ''' WITH INIT,COMPRESSION

4.2.1、INIT

若已存在相同名称备份文件，覆盖该文件片；默认为追加NOINIT

4.2.2、COMPRESSION

压缩会消耗一定的io和cpu资源；相比压缩带来的好处，建议使用压缩备份。

好处：备份文件减少10倍；备份效率提高4倍，带宽压力减少。--2005版本不包括

4.2.3、加密

加密备份是非常有必要，但是SQL Server2012并没有提供备份加密，可用rar实行压缩加密等。此处不做赘述。

4.3、远程磁盘柜共享配置

远程磁盘柜的定义即为：某个服务器磁盘的共享文件夹，将文件夹设置为共享，语句某个账号读写，利用netuse 命令完成脚本映射后，即可通过cmd命令来完成脚本拷贝删除等命令。

4.3.1、共享文件夹设置

在磁盘柜上配置共享文件夹，例如：DBADataSource文件夹共享配置，允许BackupRestoreUser读写。

4.3.2、通过net use 命令映射

--映射共享X: \\192.168.1.1\DBADataSource
EXEC master.dbo.xp_cmdshell 'net use x: \\192.168.1.1\DBADataSource "pwd" /user:"BackupRestoreUser"'
--查询映射
EXEC master.dbo.xp_cmdshell 'net use'

通过net use 完成映射磁盘，可供备份使用，封装成存储过程spx_netuse

USE [Configdb]
GO

CREATE PROC [dbo].[spx_netuse]
          @drive CHAR(1)  OUTPUT,
          @ip        VARCHAR(20),
          @directory VARCHAR(1000) = ''
WITH ENCRYPTION
AS

  DECLARE  @cmd VARCHAR(2000)
  DECLARE  @retcode int
  DECLARE  @netusetempdrives  TABLE(drive  CHAR(1),unused VARCHAR(100),status NVARCHAR(20))
  DECLARE  @netusetemp  TABLE(strs VARCHAR(2000))
  DECLARE  @errormessges VARCHAR(MAX)
  DECLARE  @unuseddrives TABLE(drive char(1))
  DECLARE @FLAG INT
  SET @FLAG = 0
  INSERT INTO @unuseddrives
  --排除ABCDEFG、Q
  SELECT  'I'
  UNION SELECT 'J'
  UNION SELECT 'K'
  UNION SELECT 'L'
  UNION SELECT 'M'
  UNION SELECT 'N'
  UNION SELECT 'O'
  UNION SELECT 'P'
  UNION SELECT 'R'
  UNION SELECT 'S'
  UNION SELECT 'T'
  UNION SELECT 'U'
  UNION SELECT 'V'
  UNION SELECT 'W'
  UNION SELECT 'X'
  UNION SELECT 'Y'
  UNION SELECT 'Z'

  IF @ip IS NULL  OR @ip NOT LIKE '%.%.%.%' OR @ip LIKE '%[a-Z]%'
    BEGIN
      SET @retcode =1000
      RAISERROR('NetUse IP IS Not Found',16,1)
      RETURN @retcode
    END
  IF  @directory IS NOT NULL AND @directory<>''   AND Charindex(':\',@directory) > 1
    BEGIN
     SET @drive = LEFT(Ltrim(Rtrim(@directory)),1)
    END

  --本地使用的磁盘
  INSERT INTO @netusetempdrives (drive,unused)
  EXEC master.dbo.Xp_fixeddrives
  --已经映射的网络磁盘
  INSERT INTO @netusetemp(strs)
  EXEC master.dbo.Xp_cmdshell 'net use'
  --将所有已经被使用的磁盘汇总
  INSERT INTO @netusetempdrives (drive,status)
  SELECT Ltrim(Rtrim(Substring(strs,Charindex('   ',strs),Charindex(':',strs) - Charindex('   ',strs)))), Ltrim(Rtrim(LEFT(strs,10))) + '-netuse'
  FROM   @netusetemp
  WHERE  strs LIKE '%[a-Z]:%'

  --如果存在已经断开的映射，则删除磁盘
  IF EXISTS (SELECT TOP 1 1
             FROM   @netusetempdrives
             WHERE  drive = LEFT(Ltrim(Rtrim(@drive)),1)
                    AND status LIKE '%已断开-netuse%' AND status LIKE '%不可用-netuse%')
    BEGIN
      SELECT @cmd = 'master..xp_cmdshell ''net use ' + LEFT(Ltrim(Rtrim(@directory)),1) + ': /del'''
      EXEC @cmd

      DELETE FROM @netusetempdrives
      WHERE  drive = LEFT(Ltrim(Rtrim(@drive)),1) AND status LIKE '%已断开-netuse%' AND status LIKE '%不可用-netuse%'
    END

  DELETE FROM @unuseddrives
  WHERE drive IN (SELECT drive from @netusetempdrives)

  IF EXISTS (SELECT TOP 1 1
             FROM   @netusetempdrives
             WHERE  drive = LEFT(Ltrim(Rtrim(@drive)),1)
                    AND status NOT LIKE '%已断开-netuse%' and status NOT LIKE '%不可用-netuse%')  --磁盘已经被映射
     or @drive is null
    BEGIN
      IF NOT EXISTS (SELECT TOP 1 1 FROM @unuseddrives)
        BEGIN
          SET @retcode =3000
          --RAISERROR('不再有可映射磁盘！！！',16,1)
          RETURN @retcode
        END
      SELECT TOP 1  @drive = drive FROM @unuseddrives
      ORDER BY NEWID()
      DELETE  FROM @unuseddrives  WHERE drive =@drive
    END

    SELECT @cmd = 'net use ' + @drive + ': \\' + @ip + '\DBADataSource' + Substring(Ltrim(Rtrim(isnull(@directory,''))),3,Len(isnull(@directory,''))) + ' "pwd" /user:"BackupRestoreUser"'    

ReNetUse:--重复映射

    BEGIN TRY
      EXEC  @retcode = master..xp_cmdshell @cmd
    END TRY
    BEGIN CATCH
       SET @retcode = 4000

       IF  @FLAG = 0
       BEGIN
           WAITFOR DELAY '00:00:10'
           SET @FLAG =1
           GOTO ReNetUse
       END
    END CATCH

  RETURN @retcode

spx_netuse

例如：

--默认路径\\' + @ip + '\DBADataSource'
--映射：映射x :\\192.168.1.1\DBADataSource
exec [dbo].[spx_netuse] @drive='X',@ip='192.168.1.1'
--映射：映射x :\\192.168.1.1\DBADataSource;若x盘已经映射，则自动分配，并output 到@drive
declare @drive char(1)='X'
exec [dbo].[spx_netuse] @drive=@drive output,@ip='192.168.1.1'

--映射：映射x :\\192.168.1.1\DBADataSource\dbbackup;若x盘已经映射，则自动分配，并output 到@drive
exec [dbo].[spx_netuse] @drive='X',@ip='192.168.1.1',@directory='x:\dbbackup'

五、spx_Backup_MoveFile归档历史备份

默认情况下，建议所有备份上传到远程磁盘柜，而磁盘柜定义如上章节（4.4、上传远程磁盘柜）

与“本地备份删除”类似，建议归档在每次完整备份执行之前归档掉。

归档策略：

1、dbbackup文件夹：存放当前备份文件；由配置表BackupRestoreSetting的copy_path字段决定。

2、dbbackuphistory\2016\201610文件夹：保留每个数据库当月最早完整备份和所有日志备份

USE [Configdb]
GO

/****************************** 功能描述：<归档备份文件>
*   创建者：<HuangCH〉
*   创建日期：<2015-12-03>
*   备注说明：<>
##########
Change Log
##########
Date                 Changer             Description
--------------------------------------------------
<2015-12-03>       <HuangCH>            <新建>

--------------------------------------------------
***************************/
CREATE PROC [dbo].[spx_Backup_MoveFile]
          @servername [SYSNAME],
          @dbname     [SYSNAME]
AS
  SET NOCOUNT ON
  --/*确认参数传递是否正确*/
  IF Db_id(@dbname) IS NULL
    BEGIN
      PRINT 'The database ' + @dbname + 'can not found'
      RETURN -1000
    END
  /*确认配置表信息是否存在*/
  IF NOT EXISTS (SELECT TOP 1 1
                 FROM   dbo.BackupRestoreSetting WITH (nolock)
                 WHERE  servername = @servername
                        AND dbname = @dbname)
    BEGIN
      RAISERROR ('backuprestoresetting配置表信息不存在，请确认',16,1)
      RETURN -2000
    END
  DECLARE  @retcode INT
  DECLARE  @copy_path VARCHAR(2000)
  DECLARE  @copy_pathTO VARCHAR(2000)--迁移路径
  DECLARE  @redo int--迁移路径
  SET @redo=0
  DECLARE  @IsCopy CHAR(1)
  DECLARE  @IsBackup CHAR(10)
  DECLARE  @netdevice CHAR(1)--网络磁盘映射
  DECLARE  @net_ip VARCHAR(20)
  DECLARE  @cmd VARCHAR(2000)
  DECLARE  @date VARCHAR(10)
  DECLARE  @ErrorMsg VARCHAR(MAX)
  SET @ErrorMsg=''
  --配置信息
  SELECT @copy_path = copy_path,
         @IsCopy = IsCopy,
         @IsBackup = IsBackup,
         @net_ip = net_ip
  FROM   dbo.BackupRestoreSetting WITH (nolock)
  WHERE  servername = @servername
         AND dbname = @dbname

  IF @IsCopy = 'T'
    BEGIN
      /*添加@netdevice映射*/
      SET @netdevice = Substring(Ltrim(@copy_path),1,1)
      EXEC spx_NetUse @drive = @netdevice output, @ip = @net_ip

      SET @copy_path=@netdevice + Substring(@copy_path,2,Len(@copy_path))
      SET @date=CONVERT(VARCHAR(6),GETDATE(),112)
      redo:--备份归档入口
      SET @copy_pathTO=@netdevice + ':\dbbackuphistory\'+CONVERT(VARCHAR(4),@date,112)+'\'+ @date+'\'+@servername
      --当前路径是否存在
      SET @cmd = 'DIR ' + @copy_pathTO
      EXEC @retcode = master..Xp_cmdshell @cmd
      PRINT @cmd
      IF @@ERROR = 0 AND @retcode <> 0
      BEGIN
           SELECT @cmd = 'MD ' + @copy_pathTO
           EXEC @retcode = master..Xp_cmdshell @cmd
           --PRINT @cmd
      END
      ELSE--存在路径
      BEGIN
          --FULL 是否存在
          SET @cmd = 'DIR ' + @copy_pathTO+'\'+ @dbname + '_'+@date+'*_FULL_*.BAK'
          --PRINT @cmd
          EXEC @retcode = master.dbo.Xp_cmdshell @cmd
          IF @@ERROR = 0 AND @retcode = 0
          BEGIN
             --删除FULL
             SET @cmd = 'DEL ' + @copy_path+ '\' + @dbname + '_'+@date+'*_FULL_*.BAK'
             EXEC @retcode = master.dbo.Xp_cmdshell @cmd
             --PRINT @cmd
          END
      END
      --删除DIFF
      SET @cmd = 'DEL ' + @copy_path+ '\' + @dbname + '_'+@date+'*_DIFF_*.BAK'
      EXEC @retcode = master.dbo.Xp_cmdshell @cmd
      --PRINT @cmd

      /*迁移备份文件数据*/
      BEGIN TRY
          SET @cmd='MOVE '+ @copy_path+ '\' + @dbname + '_'+@date+'*.BAK '+@copy_pathTO
          EXEC @retcode = master.dbo.Xp_cmdshell @cmd
          --PRINT @cmd
          IF @redo=0--上个月的转移
          BEGIN
           SET @date=CONVERT(VARCHAR(6),dateadd(mm,-1,GETDATE()),112)
           SET @redo=1
           GOTO redo
          END
      END TRY
      BEGIN CATCH
          /*删除@netdevice映射*/
          SET @ErrorMsg=@ErrorMsg+@dbname+'归档失败：'+ERROR_MESSAGE()
      END CATCH
      /*删除@netdevice映射*/
      WAITFOR DELAY '00:00:10'
      SELECT @cmd = 'net use ' + @netdevice + ':' + ' /delete /y'
      EXEC master.dbo.Xp_cmdshell @cmd
    END
    IF @ErrorMsg <> ''
    BEGIN
      RAISERROR (@ErrorMsg,16,1)
    END

spx_Backup_MoveFile

六、自动化部署

策略制定：

• 完整备份：每周一执行一次
• 差异备份：每天执行一次，周一不执行
• 日志备份：每1小时一次

6.1、新建配置表

BackupRestoreSetting

BackupFilelist

6.2、配置磁盘柜共享

若不上传远程，可排除远程共享配置；配置信息写入的时候设置isCopy=’F’即可

spx_netuse

6.3、新建存储过程

spx_Backup_DelFile

spx_Backup_DB

spx_Backup_MoveFile

以上存储过程为单个数据库的备份处理，自动化还需根据以上策略封装一层，方便Job调用和管理员维护。具体如下存储过程spb_Backup_DB

USE [Configdb]
GO
/****** Object:  StoredProcedure [dbo].[spb_Backup_DB]    Script Date: 2016/10/19 16:12:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****************************** 功能描述：<自动备份>
*   创建者：<HuangCH〉
*   创建日期：<2015-12-03>
*   备注说明：<
1、备份类型只能是"FULL OR DIFF","LOG"'
2、若需要单独执行备份，需要调用spx_Backup_DB
3、默认每周一FULL 其他时间为DIFF
>
##########
Change Log
##########
Date                 Changer             Description
--------------------------------------------------
<2015-12-03>       <HuangCH>            <新建>

--------------------------------------------------
***************************/

CREATE PROC [dbo].[spb_Backup_DB]
          @servername SYSNAME,--服务器名称@@servername
          @backupset  VARCHAR(10),--备份类型
          @Retain_weeks INT =0
AS
  IF @backupset NOT IN ('FULL&DIFF','LOG')
    BEGIN
      PRINT '@backupset备份类型只能是"FULL&DIFF","LOG"'
      RETURN -1000
    END
--  IF @servername <> @@SERVERNAME
--    BEGIN
--      PRINT '@servername服务器与本机服务器名称[' + @@SERVERNAME + ']不同'
--      RETURN -2000
--    END
  DECLARE  @dbname SYSNAME
  DECLARE  @cmd VARCHAR(1000)
  DECLARE  @errormessage VARCHAR(1000)
  SET @errormessage = ''
  IF EXISTS (SELECT TOP 1 1
             FROM   dbo.BackupRestoreSetting WITH (nolock)
             WHERE  servername = @servername AND IsBackup<>'F')
    BEGIN
      IF (@backupset = 'LOG')
        BEGIN
          --if  not exists(
          --select top 1 1
          --from  msdb.dbo.sysjobs a join msdb.dbo.sysjobactivity b  on a.job_id = b.job_id
          --where  a.name like '%完整备份%'  and b.start_execution_date is not null and b.stop_execution_date is null)
          begin
          --定义游标
          DECLARE cur_autoback CURSOR  FOR
          SELECT dbname
          FROM   BackupRestoreSetting WITH (nolock)
          WHERE  servername = @servername
                 AND dbname IN (SELECT name
                              FROM   sys.databases
                              WHERE  recovery_model <> 3--筛选简单恢复模式
                                     AND state = 0)
                 AND IsBackup<>'F'
          OPEN cur_autoback
          FETCH NEXT FROM cur_autoback INTO @dbname
          WHILE @@FETCH_STATUS = 0
            BEGIN
              BEGIN TRY
                --备份
                SET @cmd = 'dbo.spx_Backup_DB @servername=''' + @servername + ''',@dbname=''' + @dbname + ''',@backupset=''' + @backupset + ''''
                EXEC( @cmd)

              END TRY
              BEGIN CATCH
                SET @errormessage = @errormessage+'自动备份' + @backupset + '失败:  ' + Error_message()
              END CATCH
              FETCH NEXT FROM cur_autoback INTO @dbname
            END
          CLOSE cur_autoback
          DEALLOCATE cur_autoback
          end
        END
      IF (@backupset = 'FULL&DIFF')
        BEGIN
          --定义游标
          DECLARE cur_autoback CURSOR  FOR
          SELECT dbname
          FROM   dbo.BackupRestoreSetting WITH (nolock)
          WHERE  servername = @servername
                 AND dbname IN (SELECT name
                              FROM   sys.databases
                              WHERE  state = 0)--ONline数据库的备份
                AND IsBackup<>'F'
          OPEN cur_autoback
          FETCH NEXT FROM cur_autoback INTO @dbname
          WHILE @@FETCH_STATUS = 0
            BEGIN
              BEGIN TRY
                SET DATEFIRST 7
                IF  datepart(WEEKDAY,getdate())= 2--周一完整备份
                BEGIN
                --归档
                  SET @backupset='FULL'
                  SET @cmd = 'dbo.spx_Backup_MoveFile @servername=''' + @servername + ''',@dbname=''' + @dbname + ''''
                  EXEC( @cmd)
                --删除本地备份
                  SET @cmd = 'dbo.spx_Backup_DelFile @servername=''' + @servername + ''',@dbname=''' + @dbname + ''',@Retain_weeks=' + CONVERT(VARCHAR(10),@Retain_weeks)
                  EXEC( @cmd)
                --备份
                  SET @cmd = 'dbo.spx_Backup_DB @servername=''' + @servername + ''',@dbname=''' + @dbname + ''',@backupset=''' + @backupset + ''''
                  EXEC( @cmd)
                END
                ELSE
                BEGIN
                  SET @backupset='DIFF'
                --备份
                  SET @cmd = 'dbo.spx_Backup_DB @servername=''' + @servername + ''',@dbname=''' + @dbname + ''',@backupset=''' + @backupset + ''''
                  EXEC( @cmd)
                END

              END TRY
              BEGIN CATCH
                SET @errormessage = @errormessage+'自动备份' + @backupset + '失败:  ' + Error_message()
              END CATCH
              FETCH NEXT FROM cur_autoback INTO @dbname
            END
          CLOSE cur_autoback
          DEALLOCATE cur_autoback
        END
    END

    IF  @errormessage<>''
    BEGIN
      RAISERROR (@errormessage,16,1)
    END
    RETURN 0

spb_Backup_DB

6.4、新建作业

6.4.1 完整差异备份

步骤一、spb_Backup_DB @servername='sqlmonitor'，@backupset='FULL&DIFF'

执行计划、每天12点执行一次

6.4.2 日志备份

步骤一、spb_Backup_DB @servername='sqlmonitor'，@backupset='LOG'

执行计划、每1小时执行一次

支持原创，转载请注明出处！ http://www.cnblogs.com/chhuang/p/5977312.html