SQL Server 批量主分区备份(One Job)
2014-01-28 12:18 听风吹雨 阅读(3471) 评论(8) 编辑 收藏 举报一.本文所涉及的内容(Contents)
- 本文所涉及的内容(Contents)
- 背景(Contexts)
- 案例分析(Case)
- 实现代码(SQL Codes)
- 主分区完整、差异还原(Primary Backup And Restore)
- 参考文献(References)
二.背景(Contexts)
在我的数据库实例中,有很多下图所示的数据库,这些数据库的名称是有规律的,每个数据库包含的表都是相同的,其中2个表是类似流水记录的表,表的数据量会比较大,占用的空间有几十G到上百G不等,这2个表相对于其它的配置表来说是比较不重要的。
现在有一个需求就是对数据库进行备份,允许丢失这两个表的数据,保留重要的配置表数据,你是否遇到过同样的问题呢?这个时候你会怎么做呢?你有什么方案呢?有什么方法可以快速备份这些数据库呢?
(Figure1:数据库列表)
阅读本文之前你可以先参考:SQL Server 批量完整备份
三.案例分析(Case)
通过上面的描述,其中很重要的一点就是每个数据库中有2个大表,而且这些数据是不重要的,那么我们对这2个大表做表分区,把大数据放到其它文件组中,只留重要的配置表在主文件组(PRIMARY)中,接着就可以对主文件组进行备份,这样既满足了备份重要表数据,而且不会造成备份文件过大、占用磁盘空间、备份时间过长等问题。
确定了方向之后我们接着考虑作业的问题,通过作业备份类似Figure1的数据库,有下面两种方案可供选择:
(Figure2:作业列表)
(Figure3:作业列表)
Figure2是一个方案,这些作业是可以自动化创建,但是会用到批处理,代码会复杂一点,唯一的缺点就是当新创建了数据库,是无法自动备份的(不过可以通过专门创建一个监控数据库的新建、删除状态的Job来解决这个问题)详情请参考:SQL Server 批量主分区备份(Multiple Jobs)
Figure3就是我们这篇文章需要讲述的方案,这里把所有的数据库的备份都集中到一个作业中,这个方案有以下缺点:
1) 整个备份过程是串行的;
2) 如果没有异常处理,那么后面的数据库就没有办法备份了;
3) 在作业执行的时候对服务器压力比较大(没有分散执行时间);
4) 做本身的msdb数据库中记录的作业日志也没有那么清晰,排错比较困难(只有整个作业的信息,msdb.dbo.sysjobhistory的message字段保存不了太多信息),需要自己创建表进行记录;
尽管有以上的缺点,但是也是有优点的:当新创建了一个类似的数据库(业务需要),这个时候作业也会备份这个数据,不用人工去创建作业;另外还有一个,就是当数据库多而小的时候,这个方案特别有用;下面就来讲讲这个Job实现的具体步骤。
四.实现代码(SQL Codes)
实现步骤概要:
1. 批量创建文件夹;
2. 创建维护表:[JobLog]和[ErrorLog];
3. 创建备份所有数据库的SQL脚本;
4. 创建Job执行上面的脚本;
(一) 为了方便管理备份文件,我们为每个数据库创建单独的文件夹,下面的SQL代码实现根据数据库批量创建数据库名对应的文件夹,使用了游标循环数据库名进行创建文件夹,执行cmd命令需要开启数据库的xp_cmdshell开关;
--批量创建文件夹 EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE DECLARE @DBName VARCHAR(100) DECLARE @SQL VARCHAR(1000) DECLARE CurDBName CURSOR FOR SELECT name FROM sys.databases WHERE name LIKE '%Opinion%' AND STATE =0 OPEN CurDBName FETCH NEXT FROM CurDBName INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'mkdir E:\DBBackup\' + @DBName EXEC xp_cmdshell @SQL FETCH NEXT FROM CurDBName INTO @DBName END CLOSE CurDBName DEALLOCATE CurDBName EXEC sp_configure 'show advanced options', 0 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE
执行上面的脚本后,会在E:\DBBackup\目录下创建如下图所示的文件夹:
(Figure4:创建的文件夹)
(二) 对备份的维护,我希望可以了解到所有数据库的备份情况,所以下面创建2个维护表:[JobLog]和[ErrorLog],这两个表用于记录作业的执行情况,通过这两个表,可以实现如Figure5、Figure6的效果;
--作业记录表 USE [msdb] GO CREATE TABLE [dbo].[JobLog]( [Id] [int] IDENTITY(1,1) NOT NULL, [DB_Name] [varchar](50) NULL, [Backup_Date] [int] NULL, [Backup_Time] [int] NULL, [Backup_Duration] [int] NULL, [Backup_Type] [char](4) NULL, CONSTRAINT [PK_JobLog] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --错误记录表 USE [msdb] GO CREATE TABLE [dbo].[ErrorLog]( [Id] [int] IDENTITY(1,1) NOT NULL, [DB_Name] [varchar](50) NOT NULL, [Backup_Time] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_Backup_Time] DEFAULT (getdate()), [Messages] [nvarchar](500) NULL, CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
(三) 下面的代码实现了主分区完整备份和主分区差异备份(主分区可参考:SQL Server 维护计划备份主分区),当是星期一的深夜的时候,我们做完整备份,如果是其它时候我们就做差异备份,具体是什么时候,这个就通过计划里面的时候来控制了(计划的执行时间为星期一、星期三、星期五,这就代表星期一深夜做了完整备份、星期三和星期五分别做了差异备份)。(备份实践可参考:SQL Server 2008 维护计划实现数据库备份)
下面是生成的备份文件命名的范例,这样的备份文件的命名可以方便维护,而且直观知道备份文件创建的时间,可以精确到秒,文件名重复的几率不大;
DBName _Primary_Full_2013_01_14_002007.bak
DBName_Primary_Diff_2013_01_16_002034.bak
下面是整个批量备份数据库的核心SQL脚本,如果你是创建维护计划,那可以把这个SQL放到“执行 T-SQL 语句”任务,如果是创建Job的,可以放到作业的步骤里;
--批量备份数据库 DECLARE @DBName VARCHAR(100) DECLARE @CurrentTime VARCHAR(50) DECLARE @FileName VARCHAR(200) DECLARE @WithType CHAR(20) DECLARE @Backup_Date VARCHAR(50) DECLARE @Backup_Time VARCHAR(50) DECLARE @Backup_Duration VARCHAR(50) DECLARE @Backup_Start DATETIME DECLARE @Backup_End DATETIME DECLARE @BackupType CHAR(4) DECLARE @SQL VARCHAR(MAX) --防止作业遗漏备份 INSERT INTO [msdb].[dbo].[JobLog]([DB_Name],[Backup_Date],[Backup_Time],[Backup_Duration],[Backup_Type]) SELECT name,0,0,0,NULL FROM sys.databases WHERE name LIKE '%Opinion%' AND STATE =0 AND name NOT IN (SELECT DISTINCT [DB_Name] FROM [msdb].[dbo].[JobLog]) ORDER BY name DECLARE CurDBName CURSOR FOR SELECT name FROM sys.databases WHERE name LIKE '%Opinion%' AND STATE =0 ORDER BY name OPEN CurDBName FETCH NEXT FROM CurDBName INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN --Execute Backup --捕获异常 BEGIN TRY PRINT @DBName SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','') IF(DATEPART(DW, GETDATE()) = 2)--星期一 BEGIN SET @FileName = 'E:\DBBackup\'+@DBName+'\'+@DBName+'_Primary_Full_' + @CurrentTime+'.bak' SET @WithType = ' FORMAT' SET @BackupType = 'Full' END ELSE BEGIN SET @FileName = 'E:\DBBackup\'+@DBName+'\'+@DBName+'_Primary_Diff_' + @CurrentTime+'.bak' SET @WithType = ' DIFFERENTIAL,FORMAT' SET @BackupType = 'Diff' END SET @Backup_Start = GETDATE() SET @SQL = ' --1设置完整模式 ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH NO_WAIT; --2备份主分区 BACKUP DATABASE ['+@DBName+'] FILEGROUP=''PRIMARY'' TO DISK='''+@FileName+''' WITH '+@WithType+'; --3设置简单模式 ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT; ' EXEC(@SQL) SET @Backup_End = GETDATE() SET @Backup_Date = CONVERT(VARCHAR, GETDATE(),112) SET @Backup_Time = REPLACE(CONVERT(VARCHAR, GETDATE(),24),':','') SET @Backup_Duration = CONVERT(VARCHAR,DATEDIFF(ss,@Backup_Start,@Backup_End)) PRINT @Backup_Date +@Backup_Time +@Backup_Duration SET @SQL = ' INSERT INTO [msdb].[dbo].[JobLog]([DB_Name],[Backup_Date],[Backup_Time],[Backup_Duration],[Backup_Type]) VALUES('''+@DBName+''','+@Backup_Date+','+@Backup_Time+','+@Backup_Duration+','''+@BackupType+'''); ' EXEC(@SQL) END TRY BEGIN CATCH INSERT INTO [dbo].[ErrorLog]([DB_Name],[ErrorMessage]) VALUES(@DBName,ERROR_MESSAGE()) --ROLLBACK TRANSACTION END CATCH --Get Next DataBase FETCH NEXT FROM CurDBName INTO @DBName END CLOSE CurDBName DEALLOCATE CurDBName
这个备份脚本中使用了游标循环获取数据库名进行备份,在【--防止作业遗漏备份】标签的SQL语句是为了保证记录表[JobLog]每次执行都有新的记录,即使备份失败(如何查看后面会讲到)也可以观察到对应的记录;
脚本中加入了异常处理,可以有效的防止某个数据库备份失败后,后面数据库的备份不受影响,把异常信息插入到[ErrorLog]。
SQL代码里面强制了星期一进行主分区的完整备份,其它什么时候做差异备份,这个就完全由作业中计划来控制(如果你想,你可以通过作业中的计划来调整每天都进行差异备份)。
(四) 下面的代码实现了删除备份文件,从下面的代码实现删除14天之前的备份文件,这个可以作为第三步骤的下一个步骤,但是需要注意有相应的机制可以检测到备份失败的数据库,不然一段时间备份都失败了,会造成最后没有了备份文件(可以通过邮件查询[ErrorLog]进行预警,可以参考:SQL Server 创建数据库邮件)
--删除14天之前的备份文件 DECLARE @DeleteDate DATETIME SET @DeleteDate = DATEADD(DAY, -14, GETDATE()) EXECUTE MASTER.SYS.XP_DELETE_FILE 0, N'E:\DBBackup\', N'bak', @DeleteDate
(五) 查看作业的运行情况;
--行转列(备份类型) DECLARE @s NVARCHAR(MAX) SET @s='' SELECT @s=@s+','+quotename([Backup_Date])+'=MAX(CASE WHEN [Backup_Date]='+quotename([Backup_Date],'''')+' THEN [Backup_Type] ELSE NULL END)' FROM [msdb].[dbo].[JobLog] GROUP BY [Backup_Date] ORDER BY [Backup_Date] PRINT @s EXEC('SELECT [DB_Name] '+@s+' FROM [msdb].[dbo].[JobLog] GROUP BY [DB_Name] ORDER BY [DB_Name]')
(Figure5:作业备份类型)
--行转列(执行时间) DECLARE @s NVARCHAR(MAX) SET @s='' SELECT @s=@s+','+quotename([Backup_Date])+'=MAX(CASE WHEN [Backup_Date]='+quotename([Backup_Date],'''')+' THEN [Backup_Duration] ELSE NULL END)' FROM [msdb].[dbo].[JobLog] GROUP BY [Backup_Date] ORDER BY [Backup_Date] PRINT @s EXEC('SELECT [DB_Name] '+@s+' FROM [msdb].[dbo].[JobLog] GROUP BY [DB_Name] ORDER BY [DB_Name]')
(Figure6:作业执行时间)
五.主分区完整、差异还原(Primary Backup And Restore)
既然做了上面主文件组的备份,当然我们需要去测试这个主文件组的还原了,这样才可以当遇到问题可以快速还原备份文件,达到还原数据的目的;
接下来会在另外一篇文章里面专门讲解;
六.参考文献(References)
sp_update_schedule (Transact-SQL)
作者:听风吹雨
出处:
http://www.cnblogs.com/gaizai/
邮箱:gaizai@126.com
版权:本文版权归作者和博客园共有
转载:欢迎转载,必须保留原文链接
格言:不喜欢是因为不会 && 因为会所以喜欢