为了能够恢复数据，数据库运维基础就是备份，备份自动化也是运维自动化首要进行的。
笔者的备份自动化，通过配置表快速配置为前提，同时记录备份过程，尽可能的减少人工操作。首先将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小时执行一次
|
支持原创，转载请注明出处！ |