SQL Server 维护计划备份主分区

标签: sql server 计划 | 发表时间:2011-10-09 11:52 | 作者:听风吹雨 Bloger
出处:http://www.cnblogs.com/

一、场景

经过一段时间表分区的实践,我们先对表进行分区(形成表分区模板);表数据搬迁模板(迁移数据到新的分区表);分区管理自动化(自动化进行交换分区);详情请见:SQL Server 表分区实战系列(文章索引)

       再进一步延伸,我们就需要对这些做了表分区的库进行备份了,之前写过一篇博文:SQL Server 备份和还原全攻略,这里描述了MSSQL的一些备份概念,今天这里虽然没有用到,但是像差异备份在备份比较大的情况下使用就会有很好的效果。

       今天我们就来说说如何使用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进行备份

作者: 听风吹雨 发表于 2011-10-09 11:52 原文链接

评论: 0 查看评论 发表评论


最新新闻:
· 逆天搏命乔布斯(2011-10-09 16:30)
· 全球网站总量突破5亿个 “活的”1.5亿个(2011-10-09 16:27)
· 诺基亚首款WP7手机上市时间曝光(2011-10-09 15:56)
· 消息证实苹果iPhone 4S将要增加AT&T 4G指标(2011-10-09 15:12)
· 国内第一代“果粉”讲述苹果电脑对广东贡献(2011-10-09 15:11)

编辑推荐:最老程序员创业札记:全文检索、数据挖掘、推荐引擎应用41

网站导航:博客园首页  我的园子  新闻  闪存  小组  博问  知识库

相关 [sql server 计划] 推荐:

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

SQL Server 面试

- - SQL - 编程语言 - ITeye博客
在SQL语言中,一个SELECT…FROM…WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询. 子查询分为嵌套子查询和相关子查询两种. 嵌套子查询的求解方法是由里向外处理,即每个子查询在其上一级查询处理之前求解,子查询的结果作为其父查询的查询条件. 子查询只执行一次,且可以单独执行;.

SQL Server 维护计划备份主分区

- Bloger - 博客园-首页原创精华区
经过一段时间表分区的实践,我们先对表进行分区(形成表分区模板);表数据搬迁模板(迁移数据到新的分区表);分区管理自动化(自动化进行交换分区);详情请见:SQL Server 表分区实战系列(文章索引).        再进一步延伸,我们就需要对这些做了表分区的库进行备份了,之前写过一篇博文:SQL Server 备份和还原全攻略,这里描述了MSSQL的一些备份概念,今天这里虽然没有用到,但是像差异备份在备份比较大的情况下使用就会有很好的效果.

SQL Server优化50法

- - CSDN博客推荐文章
虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷).   I/O吞吐量小,形成了瓶颈效应.   没有创建计算列导致查询不优化.   内存不足网络速度慢查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 中的事务

- - CSDN博客推荐文章
       事务要有非常明确的开始和结束点,SQL Server 中的每一条数据操作语句,例如SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分. 即使只有一条语句,系统也会把这条语句当做一个事务,要么执行所有的语句,要么什么都不执行.         事务开始之后,事务所有的操作都会写到事务日志中,写到日志中的事务,一般有两种:一是针对数据的操作,例如插入、修改和删除,这些操作的对象是大量的数据;另一种是针对任务的操作,例如创建索引.

SQL Server优化50法

- - CSDN博客数据库推荐文章
  虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:. 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷). I/O吞吐量小,形成了瓶颈效应. 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 查询步骤 - pursuer.chen

- - 博客园_首页
标签:SQL SERVER/MSSQL SERVER/数据库/DBA/查询步骤.       查询步骤是很基础也挺重要的一部分,但是我还是在周围发现有些人虽然会语法,但是对于其中的步骤不是很清楚,这里就来分解一下其中的步骤,在技术内幕系列里面都会有讲到.  TOP于ORDER BY的关系. INSERT INTO Customers VALUES(1,'深圳'),(2,'广州'),(3,'武汉'),(4,'上海'),(5,'北京').

sql server复灾 你懂了吗?

- brett80 - 博客园-首页原创精华区
很多时候我们不小心错误delete了一下,或者update一下怎么办,或者直接把数据库删除了,怎么办呢,是不是就一定没有办法呢. 下面让我来教大家我现学现卖的两招. 做之前我们要设置数据库恢复模式:. 首先我们创建一个表:插入几条数据. 我们现在有五条数据了,我们对数据做一个备份. 做任何差异备份,和日志之前,一定要做一个完整备份.

监控 SQL Server 的运行状况

- Bloger - 博客园-首页原创精华区
Microsoft SQL Server 2005 提供了一些工具来监控数据库. 动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能. 常规服务器动态管理对象包括:. dm_db_*:数据库和数据库对象. dm_exec_*:执行用户代码和关联的连接.

SQL Server 数据库巡检脚本

- - CSDN博客数据库推荐文章
select '现在没有阻塞和死锁信息' as message. select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'. select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'.