SQL Server 批量主分区备份（Multiple Jobs）

最新推荐文章于 2024-04-26 11:11:22 发布

原创

最新推荐文章于 2024-04-26 11:11:22 发布 · 415 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了在SQL Server中批量备份主分区的方案，针对包含大量数据的表，通过分区将重要数据保留在主文件组，实现主文件组的完整和差异备份。文中提供了三种解决方案，包括使用维护计划、自定义作业以及为每个数据库创建单独作业，并给出了相应的SQL代码示例。重点讨论了如何控制备份时间、创建作业和调整作业执行计划。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.本文所涉及的内容（Contents）

本文所涉及的内容（Contents）
背景（Contexts）
案例分析（Case）
1. 方案一（Solution One）
2. 方案二（Solution Two）
3. 方案三（Solution Three）
实现代码（SQL Codes）
主分区完整、差异还原（Primary Backup And Restore）
参考文献（References）

二.背景（Contexts）

　　在我的数据库实例中，有很多类似下图所示的数据库，这些数据库的名称是有规律的，每个数据库包含的表都是相同的，其中2个表是类似流水记录的表，表的数据量会比较大，占用的空间有几十G到上百G不等，这2个表相对于其它的配置表来说是比较不重要的。

　　现在有一个需求就是对数据库进行备份，允许丢失这两个表的数据，保留重要的配置表数据，你是否遇到过同样的问题呢？这个时候你会怎么做呢？你有什么方案呢？有什么方法可以快速备份这些数据库呢？

wps_clip_image-2334

（Figure1：数据库列表）

阅读本文之前你可以先参考：SQL Server 批量完整备份

三.案例分析（Case）

　　通过上面的描述，其中很重要的一点就是每个数据库中有2个大表，而且这些数据是不重要的，那么我们对这2个大表做表分区，把大数据放到其它文件组中，只留重要的配置表在主文件组（PRIMARY）中，接着就可以对主文件组进行备份，这样既满足了备份重要表数据，而且不会造成备份文件过大、占用磁盘空间、备份时间过长等问题。

方案一：

　　使用维护计划；请参考：SQL Server 维护计划备份主分区，只要在【执行T-SQL语句】的任务中使用循环递归所有数据库进行备份，在【清除历史记录】任务和【清除维护】任务中选择父目录就可以了；

方案二：

　　方案一中通过维护计划生成的作业是看不到具体的备份代码的，所以方案二就是为了补充方案一的。如果你没有作业执行时间的特殊要求，你可以创建一个作业，循环数据库名称进行主分区的备份，只是把维护计划【执行T-SQL语句】的内容放到了作业中；

这个方案有以下几个缺点：

a) 没有办法单独控制某个数据库备份的时间；

b) 数据库一多的话，在Job的Message里面没有办法显示那么多的信息；

c) 备份串行执行的，没有办法进行并行备份；

d) 个个数据库的备份不是单独的，如果作业出错，会造成后面的数据库无法备份；

e) 貌似是先写入内存，等作业完成后才一次性写入硬盘的？当数据库备份文件比较大的时候会莫名只备份几个数据库作业就退出了，没有查明是什么原因；

具体的操作步骤可以参考：SQL Server 批量主分区备份（One Job），这个方案最终的作业形式为：

wps_clip_image-26885

（Figure2：作业列表）

方案三：

　　方案三是为了控制备份的时间而准备的，就是每个数据库都创建一个作业，这样的好处是可以充分的控制到每个数据库，因为我们可以需要对某个数据库进行完整备份，而且备份的粒度也有可能不同（重要客户的备份粒度会更小）

wps_clip_image-16066

（Figure3：作业列表）

　　既然我们选择了方案三，那我们如何快速（批量）创建这些作业呢？我们先以数据库Barefoot.Opinion.9197为例，创建出一个作业的模板后，通过修改、替换这个作业的代码来实现批量生成新的可执行的作业脚本；

通过下面的几个步骤你就可以批量的生成备份作业：

1) 备份主分区完整备份的SQL代码（备份配置表）；备份主分区差异备份的SQL代码（减轻备份文件空间压力）

2) 自动删除备份文件的SQL代码；（保证磁盘有足够空间）

3) 批量创建文件夹；（每个数据库单独一个文件夹）

4) 批量创建作业方案；（每个作业的名称、目录都不相同）

5) 批量修改作业计划的时间；（均衡分配作业的执行时间）

6) 批量删除作业；（方便维护）

7) 查看作业的执行情况；（防止作业时间过长，过长可以考虑预警）

四.实现代码（SQL Codes）

(一) 下面的代码实现了主分区完整备份和主分区差异备份，当是星期一的深夜的时候，我们做完整备份，如果是其它时候我们就做差异备份，具体是什么时候，这个就通过计划里面的时候来控制了（计划的执行时间为星期一、星期三、星期五，这就代表星期一深夜做了完整备份、星期三和星期五分别做了差异备份）。

生成的备份文件名为：（这样的文件名方便阅读；而且能精确到秒，重复的几率不大）

DBName _Primary_Full_2013_01_14_002007.bak

DBName_Primary_Diff_2013_01_16_002034.bak

--1设置完整模式
USE [master]
GO
ALTER DATABASE [DBName.9197] SET RECOVERY FULL WITH NO_WAIT
GO

--2备份主分区
DECLARE @CurrentTime VARCHAR(50), @FileName VARCHAR(200)
SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')
IF(DATEPART(DW, GETDATE()) = 2)--星期一
BEGIN
    --完整备份
    SET @FileName = 'E:\DBBackup\DBName.9197\DBName.9197_Primary_Full_' + @CurrentTime+'.bak'
    BACKUP DATABASE [DBName.9197]
    FILEGROUP='PRIMARY' TO DISK=@FileName WITH FORMAT
END
ELSE
BEGIN
    --差异备份
    SET @FileName = 'E:\DBBackup\DBName.9197\DBName.9197_Primary_Diff_' + @CurrentTime+'.bak'
    BACKUP DATABASE [DBName.9197]
    FILEGROUP='PRIMARY' TO DISK=@FileName WITH DIFFERENTIAL,FORMAT
END
GO

--3设置简单模式
USE [master]
GO
ALTER DATABASE [DBName.9197] SET RECOVERY SIMPLE WITH NO_WAIT
GO