（4.21）sql server备份策略深入探究
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
例如：删除本地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 '05'
WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '10%' THEN '08'
WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '11%' THEN '12'
WHEN CONVERT(VARCHAR(20),Serverproperty('ProductVersion')) LIKE '12%' THEN '14'
ELSE '16' 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
以上备份存储过程可执行三种类型备份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
例如：
--默认路径\\' + @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文件夹：保留每个数据库当月最早完整备份和所有日志备份
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
spb_Backup_DB6.4、新建作业
6.4.1 完整差异备份
步骤一、spb_Backup_DB @servername='sqlmonitor'，@backupset='FULL&DIFF'
执行计划、每天12点执行一次
6.4.2 日志备份
步骤一、spb_Backup_DB @servername='sqlmonitor'，@backupset='LOG'
执行计划、每1小时执行一次
【7】我的最佳实践脚本
【7.1】脚本
CREATE PROCEDURE [dbo].[Generalization_Backup] @Database_Name NVARCHAR(50), --输入_备份数据库名称 @Bakup_Type INT, --输入_备份类型:1完整备份,2差异备份,3事务日志 @Bakup_Folder NVARCHAR(200), --输入_备份文件夹名 @Out_Message NVARCHAR(400) OUT --输出_输出消息 /* 创建者:zachary 创建时间:2011年6月24日 功能说明:通用备份,输入备份数据库名称、备份类型、备份文件夹名就执行相应的备份操作 */ AS BEGIN --==========创建需要的一些变量,拼接备份文件夹 DECLARE @Bakup_Path NVARCHAR(200), --备份路径 @SQL NVARCHAR(MAX), --动态语句 @Day NCHAR(8), --备份时间(年月日) @Date NCHAR(11), --备份时间(年月日_小时) @Path NVARCHAR(200), --备份路径 @Bakup_Name NVARCHAR(50) --备份文件名 DECLARE @t_driver TABLE (Driver CHAR(1),MB_Free INT) -- 该表变量保存各分区的剩余空间信息 INSERT INTO @t_driver EXEC master.dbo.xp_fixeddrives -- 填充分区剩余空间信息 SET @Bakup_Path = (SELECT MAX(Driver) FROM @t_driver WHERE Driver IN('C','D','E','F')) SET @Bakup_Path = @Bakup_Path +':\'+@Bakup_Folder+'\' --PRINT @Bakup_Path EXEC MASTER.dbo.xp_create_subdir @Bakup_Path -- 创建备份文件夹 SET @Day=CONVERT(NCHAR(8),GETDATE(),112) SET @Date=CONVERT(NCHAR(8),GETDATE(),112)+'_'+RIGHT(CONVERT(NCHAR(13),GETDATE(),120),2) --PRINT @Day --PRINT @Date --==========检查输入_备份数据库名称是否有效 IF NOT EXISTS(SELECT TOP 1 NULL FROM sys.databases WHERE NAME=@Database_Name) BEGIN SET @Out_Message='未找到名为'+@Database_Name+'的数据库,请检查输入数据库名' RAISERROR (@Out_Message,16,1) RETURN -1 END --==========检查输入_备份路径是否有效 -- DECLARE @t1 TABLE(subdirectory NVARCHAR(100),depth INT) --该表变量保存输入文件夹下的所有文件夹名和文件名 -- SET @Path=SUBSTRING(@Bakup_Path,1,LEN(@Bakup_Path)-LEN(@Bakup_Folder)-1) -- INSERT INTO @t1 EXEC xp_dirtree @Path,1 -- IF @Bakup_Folder NOT IN (SELECT subdirectory FROM @t1) -- BEGIN -- SET @Out_Message='在路径'+@Path+'下找不到名为'+@Bakup_Folder+'的文件夹' -- RETURN -1 -- END --==========检查磁盘空间是否足够,不足则删除 DECLARE @t2 TABLE(Disk_Name NCHAR(1),Disk_Size INT) --该表变量保存磁盘剩余空间 DECLARE @Database_Size INT,@Disk_Size INT INSERT INTO @t2 EXEC xp_fixeddrives SELECT @Disk_Size=Disk_Size FROM @t2 WHERE Disk_Name=SUBSTRING(@Bakup_Path,1,1) DECLARE @sql1 NVARCHAR(2000) SET @sql1='USE '+@Database_Name+' SELECT @Database_Size=SUM(size/128) from sysfiles' EXEC sp_executesql @sql1,N'@Database_Size INT OUTPUT',@Database_Size OUTPUT IF @Disk_Size < (20480+@Database_Size) --剩余空间要大于20G+数据库所占大小之和 BEGIN --SET @Out_Message='备份路径剩余空间小于数据库,不足以支持一次完整备份,请清理空间后再进行备份' DECLARE @i INT SET @i=30 WHILE @i>=3 --从30天开始循环删除备份文件,保留近3天的 BEGIN DECLARE @Day1 NCHAR(8) SET @Day1=CONVERT(NCHAR(8),GETDATE()-@i,112) EXEC master.dbo.xp_delete_file 0,@Bakup_Path,'bak',@Day1 EXEC master.dbo.xp_delete_file 0,@Bakup_Path,'trn',@Day1 EXEC master.dbo.delete_file @i,@Bakup_Path SET @i=@i-1 DECLARE @t3 TABLE (Disk_Name1 NCHAR(1),Disk_Size1 INT) --该表变量保存磁盘剩余空间 DECLARE @Database_Size1 INT,@Disk_Size1 INT INSERT INTO @t3 EXEC xp_fixeddrives SELECT @Disk_Size1=Disk_Size1 FROM @t3 WHERE Disk_Name1=SUBSTRING(@Bakup_Path,1,1) EXEC sp_executesql @sql1,N'@Database_Size INT OUTPUT',@Database_Size1 OUTPUT IF @Disk_Size1 > (20480+@Database_Size1) SET @i=0 --一旦有足够的空间就停止循环 END END --==========修改备份文件名 IF @Database_Name='Db_Tank' SET @Bakup_Name=@Database_Name+'_Copy' ELSE SET @Bakup_Name=@Database_Name --==========完整备份 IF @Bakup_Type=1 BEGIN BEGIN try SET @SQL='BACKUP DATABASE ['+@Database_Name+'] TO DISK='''+@Bakup_Path+@Bakup_Name+CAST(DAY(GETDATE()) AS NVARCHAR(20))+'.bak'' WITH INIT,STATS = 10' --PRINT @SQL EXEC (@SQL) SET @Out_Message='执行完整备份将数据库'+@Database_Name+'备份到'+@Bakup_Path+@Database_Name+CAST(DAY(GETDATE()) AS NVARCHAR(20))+'.bak' PRINT @Out_Message RETURN 0 END TRY BEGIN CATCH SET @Out_Message='系统输出了错误信息,错误信息为:'+ERROR_MESSAGE()+'错误号为:'+CAST(ERROR_NUMBER() AS NVARCHAR(10))+',执行失败!' RAISERROR (@Out_Message,16,1) RETURN -1 END CATCH END --==========差异备份 IF @Bakup_Type=2 BEGIN --==========判断数据库是否执行过完备 IF NOT EXISTS(SELECT TOP 1 NULL FROM msdb..backupset WHERE database_name=@Database_Name AND [type]='D') BEGIN SET @Out_Message='检查到数据库'+@Database_Name+'未执行过完整备份,不能进行差异备份' RAISERROR (@Out_Message,16,1) RETURN -1 END BEGIN TRY SET @SQL='BACKUP DATABASE ['+@Database_Name+'] TO DISK='''+@Bakup_Path+@Database_Name+'_'+@Date+'.bak'' WITH DIFFERENTIAL' --PRINT @SQL EXEC (@SQL) SET @Out_Message='执行差异备份将数据库'+@Database_Name+'备份到'+@Bakup_Path+@Database_Name+'_'+@Date+'.bak' PRINT @Out_Message RETURN 0 END TRY BEGIN CATCH SET @Out_Message='系统输出了错误信息,错误信息为:'+ERROR_MESSAGE()+'错误号为:'+CAST(ERROR_NUMBER() AS NVARCHAR(10))+',执行失败!' RAISERROR (@Out_Message,16,1) RETURN -1 END CATCH END --==========事务日志备份 IF @Bakup_Type=3 BEGIN --==========判断数据库是否执行过完备 IF NOT EXISTS(SELECT TOP 1 NULL FROM msdb..backupset WHERE database_name=@Database_Name AND [type]='D') BEGIN SET @Out_Message='检查到数据库'+@Database_Name+'未执行过完整备份,不能进行事务日志备份' RAISERROR (@Out_Message,16,1) RETURN -1 END BEGIN TRY SET @SQL='BACKUP LOG ['++@Database_Name+'] TO DISK='''+@Bakup_Path+@Database_Name+'_'+@Date+'_log.trn''' --PRINT @SQL EXEC (@SQL) SET @Out_Message='执行事务日志备份将数据库'+@Database_Name+'备份到'+@Bakup_Path+@Database_Name+'_'+@Date+'_log.trn' PRINT @Out_Message RETURN 0 END TRY BEGIN CATCH SET @Out_Message='系统输出了错误信息,错误信息为:'+ERROR_MESSAGE()+'错误号为:'+CAST(ERROR_NUMBER() AS NVARCHAR(10))+',执行失败!' RAISERROR (@Out_Message,16,1) RETURN -1 END CATCH END ELSE BEGIN SET @Out_Message='输入的备份类型'+CAST(@Bakup_Type AS NVARCHAR(10))+'错误,备份类型为:1完整备份,2差异备份,3事务日志' RAISERROR (@Out_Message,16,1) RETURN -1 END END
【7.2】使用
--事务日志备份
DECLARE @return_value int, @Out_Message nvarchar(400) EXEC @return_value = [dbo].[Generalization_Backup] @Database_Name = N'Db_Tank', @Bakup_Type = 3, @Bakup_Folder = N'Db_Tank_Back', @Out_Message = @Out_Message OUTPUT SELECT @Out_Message as N'@Out_Message' SELECT 'Return Value' = @return_value GO
|
支持原创，转载请注明出处！ |
