SQL Server 维护计划备份主分区

http://www.2cto.com/database/201110/107015.html  红黑联盟

一、场景

今天我们就来说说如何使用MSSQL的维护计划来备份表分区的。

假设这样一个场景：一个数据库现在已经几十G（如图1），但是占用主要空间的就是一两个表的数据（流水记录数据），其它的就是一些配置表，我们对这些配置表数据安全性要求比较高，而对流水数据比较低，那么我们有什么方案可以保证这个数据库的数据安全呢？

（图1）

 

二、方案

方案一：对于上面的场景，我们最简单、最合理的方案就是把这两个表PostSnapshot、PostLog分离出来作为一个新的数据库A，而配置表单独作为一个数据库B，这样的好处是很多的，这样对配置数据库B的备份就简单的多了，更重要的一点就是数据库A与B的读写也分离了（频繁读取配置数据库B和频繁写记录数据库A）

方案二：但是往往在很多情况是业务上不允许我们这样做，那还有没一个可以折中的办法呢？对的，这个折中的办法就是我今天想表述的内容了，我们先对这两个表PostSnapshot、PostLog进行表分区，剩下的配置表就依然还在主分区（Primary）里面，我们只需要备份主分区就可以达到备份配置表的目的了。

对于方案二，一个比较大的缺点就是在还原备份的时候，这两个表PostSnapshot、PostLog是用不了的，也删除不了，只能通过修改表名之后再创建两个新表，不过这个缺点对于这些备份来说可以忽略，因为我们重要的数据可以通过主分区的备份找回来就已经满足我们这类备份的目的了。

 

三、实践

(一) 使用【维护计划】->【维护计划向导】，在出现的窗体中需要注意一个选项，如图2所示，需要选择【每项任务单独计划】，这样才能在作业中看到不同的任务所对应的作业，这样的好处是可以单独执行某个作业。

（图2）

(二) 这个维护计划中包括了两个子计划：Subplan_Primary与Subplan_Primary_Save，Subplan_Primary的计划是每天晚上的1点钟，图3表述了作业的执行步骤与过程：

1)     首先是执行一段我们编写的T-SQL代码，如果这里不是需要进行分区备份，而是使用完整备份或者是差异备份的话，我们完全就不需要写T-SQL代码；

2)     接着删除备份的日志记录，只保留2周的记录；

3)     最后对备份的bak文件进行维护了，保留2周内的bak备份文件，2周之前的bak会自动被删除掉；

（图3）

(三) 我们的数据库模式设置为简单模式了，在这种状态下是无法对分区进行备份的，所以：

1)     首先要把数据库的模式设置为完整模式；

2)     接着使用T-SQL备份数据库的主分区；

3)     最后再把数据库的模式设置为简单模式；

使用备份还原数据库，还原后的数据库是完整模式的呢？还是简单模式的呢？当然是完整模式啦。

（图4）

(四) 在备份主分区的代码中，我们除了图4中把DISK的路径作为参数传进去执行外，我们还可以使用拼凑SQL的方式，如图5所示；

（图5）

(五) 可能很多人都不太理解为什么上面的子计划已经做了一次主分区的备份了，为什么还要在添加一个子计划呢？其实这个是为了管理上的方便，因为Subplan_Primary的备份间隔是每天，只保留2周内备份（14个bak文件），所以太久之前的备份就会丢失了，为了能保留尽可能久的备份，我们添加了Subplan_Primary_Save子计划，它每周只执行一次，保留4个月内的备份（16个bak文件）；这样要比100多个bak要节省很多的空间。（我们可以考虑每周拷贝一份Subplan_Primary中bak到其它服务器进行备份，这样就可以省略掉Subplan_Primary_Save）

Subplan_Primary与Subplan_Primary_Save的区别：

1)     在T-SQL里面保存bak的路径是不同的；

2)     文件命名中的日期格式不一样；

3)     执行计划频率不同；

4)     清除任务的保留时间不同；

5)     清除任务bak文件的路径不同；

（图6）

(六) 在测试过程中，为了看看维护计划中【清除维护任务】的效果，我们缩短了保留的天数，设置为5天，图7是执行作业前的bak文件列表，图8是设置为保留5天并执行作业后的bak文件列表。可以看到最久的一个bak文件被删除了。

（图7）

（图8）

 

四、部分T-SQL代码

--1设置完整模式

USE [master]

GO

ALTER DATABASE [Barefoot.Ant] SET RECOVERY FULL WITH NO_WAIT

GO

 

--2备份主分区

DECLARE

 @FileName VARCHAR(200),

 @CurrentTime VARCHAR(50)

 

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

SET @FileName = 'F:\DBBackup\Ant_Primary\Ant_Primary_' + @CurrentTime

BACKUP DATABASE [Barefoot.Ant]

FILEGROUP='PRIMARY' TO DISK=@FileName WITH FORMAT

GO

 

--3设置简单模式

USE [master]

GO

ALTER DATABASE [Barefoot.Ant] SET RECOVERY SIMPLE WITH NO_WAIT

GO

 

--还原主分区

RESTORE DATABASE [TestAnt]

FILEGROUP='PRIMARY'

FROM DISK='F:\DBBackup\Ant_Primary\Ant_Primary_20110916000001.bak' WITH FILE = 1,

MOVE N'Barefoot.Ant' TO N'F:\DBBackup\TestAnt.mdf',

MOVE N'Barefoot.Ant_log' TO N'F:\DBBackup\TestAnt_log.ldf',

RECOVERY,REPLACE, STATS = 10

GO

 

五、参考文献

使用SQL Server的作业进行数据库备份

使用T-SQL进行数据库备份并检查该备份文件是否存在且作出相应处理

用命令对sql进行备份