详谈数据库的备份方案与策略.docx

详谈数据库的备份方案与策略.docx

《详谈数据库的备份方案与策略.docx》由会员分享，可在线阅读，更多相关《详谈数据库的备份方案与策略.docx（29页珍藏版）》请在冰豆网上搜索。

详谈数据库的备份方案与策略

关于SQLSERVER数据库

的维护和备份方案

参考资料：

SQLServer2008编程入门经典（第3版）

1引言

1．1为什么要进行数据备份

在信息化高速发展的今天数据在人们的生活中扮演着越来越重要的角色，信息化渗透着教育、科研、医疗等等各个领域，所以为防止数据丢失的第一道防线是实行数据备份，备份就像锻炼身体，虽然重要，但却常常被忽视，数据备份观念在一些企业中，甚至是在网络管理员中仍然得不到足够的重视。

当网站更新或添加内容时，如果实行了数据备份，那么即使出现操作失误，把有用的资讯或者重要的内容删掉了，那么也不至于让之前花了很长时间辛辛苦苦建立起来的数据付之东流，只要及时地通过系统备份和恢复方案就可以实现数据的安全性和可靠性了；又或者是企业网站发生灾难，原始数据丢失或遭到破坏，利用备份数据就可以把原始数据恢复出来，使系统能够继续正常工作。

可见，数据备份是何等的重要，数据备份是为了以后能够顺利地将被破坏了或丢失了的数据库安全地恢复的基础性工作，可以这么说，没有数据库的备份，就没有数据库的恢复，企业应当把数据备份和恢复的工作列为一项不可忽视的系统工作，为其网站选择相应的备份设备和技术，进行经济可靠的数据备份，从而避免可能发生的重大损失。

2、SQLServer备份和还原

2．1、几种常见的SQLServer数据库备份

2.1.1、完全备份

 备份全部选中的文件夹，并不依赖文件的存档属性来确定备份那些文件。

（在备份过程中，任何现有的标记都被清除，每个文件都被标记为已备份，换言之，清除存档属性）。

完全备份也叫完整备份。

2.1.2、差异备份

 差异备份是针对完全备份：

备份上一次的完全备份后发生变化的所有文件。

（差异备份过程中，只备份有标记的那些选中的文件和文件夹。

它不清除标记，即：

备份后不标记为已备份文件，换言之，不清除存档属性）。

2.1.3、增量备份

增量备份是针对于上一次备份（无论是哪种备份）：

备份上一次备份后，所有发生变化的文件。

（增量备份过程中，只备份有标记的选中的文件和文件夹，它清除标记，即：

备份后标记文件，换言之，清除存档属性。

）

2.1.4、事务日志备份

 在特定事务日志备份之前执行的完整数据库备份和上次差异备份（如果有）。

在完整数据库备份之后执行的所有事务日志备份或在特定事务日志备份之前执行的差异备份（如果您还原了差异备份）。

如果你设置了恢复模式为【简单】，你将无法使用【事务日志】备份。

SQLServer2000和SQLServer2005：

创建事务日志备份，您必须使用完整恢复或大容量日志记录恢复模型。

2.1.5部分备份

通过指定READ_WRITE_FILEGROUPS创建的备份称为“部分备份”。

在简单恢复模式下，只允许对只读文件组执行文件组备份。

还原的数据备份类型：

数据库备份、部分备份或文件备份。

对于数据库备份或部分备份，日志备份序列必须从数据库备份或部分备份的结尾处开始延续。

对于一组文件备份，日志备份序列必须从整组文件备份的开头开始延续。

2.1.6文件备份 

“文件备份”包含一个或多个文件（或文件组）中的所有数据。

2.1.7日志链

 连续的日志备份序列称为“日志链”。

日志链从数据库的完整备份开始。

通常，仅当第一次备份数据库时，或者将恢复模式从简单恢复模式切换到完整恢复模式或大容量日志恢复模式之后，才会开始一个新的日志链。

除非在创建完整数据库备份时选择覆盖现有备份集，否则现有的日志链将保持不变。

在该日志链保持不变的情况下，便可从媒体集中的任何完整数据库备份还原数据库，然后再还原相应恢复点之前的所有后续日志备份。

恢复点可以是上次日志备份的结尾，也可以是任何日志备份中的特定恢复点。

2.1.8一个备份方案例子

 某个站点在星期天晚上执行完整数据库备份。

在白天每隔4小时制作一个事务日志备份集，并用当天的备份重写头一天的备份。

每晚则进行差异备份。

如果数据库的某个数据磁盘在星期四上午9:

12出现故障，则该站点可以：

1）     备份当前事务日志；（已经出现故障了，如何备份当前事务日志？

）

2）     还原从星期天晚上开始的数据库备份；

3）     还原从星期三晚上开始的差异备份，将数据库前滚到这一时刻；

4）     还原从早上4点到8点的事务日志备份，以将数据库前滚到早上8点；

5）     还原故障之后的日志备份。

这将使数据库前滚到故障发生的那一刻。

2.2、数据库的还原

创建一个叫TestBackup的数据库，创建一张叫Table1的表，这个时候进行一次完整备份，备份文件为：

TestBackupDB-full.bak；接着创建表Table2后进行差异备份，备份文件为：

TestBackupDB-diff.bak；接着创建表Table3后进行事务日志备份（如果数据库设置了恢复模式为【简单】，那么在备份类型选项中将看不到【事务日志】），备份文件为：

TestBackupDB-log.bak；

创建一个叫TestBackup2的数据库，用于测试TestBackup数据库的备份文件的还原。

（图1：

创建库结构）

（图2：

备份类型）

       下面我们就可以对三个备份文件：

TestBackupDB-full.bak、TestBackupDB-diff.bak、TestBackupDB-log.bak进行还原：

       步骤1：

还原完整备份文件TestBackupDB-full.bak，选项如图4、图5所示，还原成功后数据列表就会如图6所示，这是因为恢复状态选项：

不对数据库执行任何操作，不回滚未提交的事务。

可以还原其他事务日志。

（RESTOREWITHNORECOVERY）

（图3：

进入SSMS还原）

（图4：

还原常规）

（图5：

还原选项）

（图6：

完整备份还原）

步骤2：

还原差异备份文件TestBackupDB-diff.bak，操作如步骤1所示，这个时候的数据库还是跟图6的状态一样的。

步骤3：

还原事务日志备份文件TestBackupDB-log.bak，如图7进入事务日志的还原操作界面；看图8的选项中有指定事务的时间进行还原（还原过程中的恢复状态都是默认为RESTOREWITHRECOVERY，所以这里没有提及这个选项）。

还原后的TestBackup2数据库，还原之后的数据库TestBackup2如图9所示。

（图7：

进入事务日志）

（图8：

事务日志）

（图9：

还原后的数据库）

2.3、升级

通常来说文章写到这里就应该结束了，但是很幸运，再给你介绍一下如何在对表进行分区后的还原操作，从上面的操作来看只包括了mdf和ldf文件，但如果多了几个ndf文件，这些还原又一样吗？

所以我称这部分的内容为升级。

情景一：

如果本来就有对应的分区文件的，只要在还原的时候修改【还原为】的文件名就可以进行还原了。

情景二：

如果刚刚新建了分区文件组和文件，这个时候接着还原备份就会出现图10的错误（不知道是不是在SQLServer2005的问题）；要解决这个问题有两个方法，第一个：

重启数据库服务再还原；第二个：

设置数据库的【限制访问】设置为【Single】；

（图10：

错误）

3、如何实现数据库备份

3.1、场景设计

假设我们的数据库不是很大，但是我们的数据会比较重要，大概一天一个备份就能满足我们的业务需要，那我们应该如何设计我们的备份方案呢？

星期天晚上02:

00做一次数据库的完全备份，其它时间星期一至星期六晚上02:

00做一次差异备份，基本逻辑如下图所示：

（图：

数据库备份文件生成逻辑）

Tips：

1.      如果你的数据库比较大，而且流数据（就是一些记录性的流水数据）比较多，我们的做法是把这些大表分离出一个新的数据库（纵向切割数据库），这样接下来的问题，这篇文章就可以解决了；

2.      如果无法把流数据分离出数据库，那么可以考虑表分区，分区之后再对其它表进行数据备份（主分区备份）；这些备份是无法使用差异备份了，每次都只能使用完全备份，而且还原的时候表分区是不可以用的，只能修改表名之后再创建新表。

详情参考：

SQLServer2008编程入门经典（第3版）

3.      如果一天一次的备份满足不了业务上的需求，那么可以考虑发布订阅方案，在两台机器上进行事务复制，可以调整复制的频率，加强数据的安全性。

3.2、实战操作

1.      数据库->【管理】->【维护计划】 ->【新建维护计划】；

（图1）

2.      勾选【备份数据库（完整）】和【备份数据库（差异）】；

（图2）

3.      设置 完整备份 文件的路径和备份文件的扩展名；注意特定数据库的选择；

（图3）

4.      填写 完整备份 作业计划的名称，执行间隔勾选星期日，设置执行时间；

（图4）

5.      设置 差异备份 文件的路径和备份文件的扩展名；注意特定数据库的选择；

（图5）

6.      填写 差异备份 作业计划的名称，执行间隔勾选除星期日之外的日期，设置执行时间；

（图6）

7.      这就生成 完整备份与差异备份 的子计划了，

（图7）

8.      在完整备份的设计界面中，从左边拖拉一个【清除历史记录】的控件到右边，并双击进行下图的设置；

（图8）

9.      从左边拖拉一个【清除维护】的控件到右边，并双击进行下图的设置，这里需要设置 完整备份 的路径和文件的扩展名；

（图9）

10.   在步骤上加上箭头形成 完全备份 作业的流程图；

（图10）

11.   在差异备份的设计界面中，从左边拖拉一个【清除历史记录】的控件到右边，并双击进行下图的设置；

（图11）

12.   从左边拖拉一个【清除维护】的控件到右边，并双击进行下图的设置，这里需要设置 差异备份 的路径和文件的扩展名；

（图12）

13.   在步骤上加上箭头形成 差异备份 作业的流程图；

（图13）

14.   在维护计划下生成Ant_Backup，在代理中生成 完整备份与差异备份的作业；

（图14）

4、SQLServer维护计划实现数据库备份（策略实战方案）

上面文章使用完整备份和差异备份基本上能解决数据库备份的问题，但是为了保障数据更加安全，我们需要再次完善我们的备份计划；

下面这篇文章主要加入了日志备份，并对设计备份的频率和设计命名规范等问题进行实战；

4.1、最佳实践

4.1.1、备份计划

1）每周星期日的2:

00:

00执行数据库的完整备份；

2）每周星期一至星期六每天的2:

00:

00执行数据库的差异备份；

3）每天在8:

00:

00和23:

59:

59之间、每1小时执行数据库的日志备份；

4）每个月的最后一个星期日的1:

00:

00执行数据库的完整备份；

4.1.2、计划讲解

1.根据备份文件的不同，我创建了4个文件用于存放备份文件：

（Medusa是数据库名）

1）Medusa_Full：

用于存放完整备份文件；

2）Medusa_Diff：

用于存放差异备份文件；

3）Medusa_Log：

用于存放日志备份文件；

4） Medusa_Save：

用于存放保留备份文件；

上面的4个文件夹与备份计划的4个步骤是一一对应的；

（Figure1：

文件夹命名规范）

2.创建名为Medusa的维护计划，并创建如下图的4个子计划：

（Figure2：

子计划列表）

3.设置【清除历史记录】、【清除维护】的保留时间：

1）Subplan_Full：

保留完整备份文件4周；（1个*4周=4个文件）

2）Subplan_Diff：

保留差异备份文件2周；（6个*2周=12个文件）

3）Subplan_Log：

保留日志备份文件1周；（16个*7天=112个文件）

4）Subplan_Save：

保留备份文件1年；（1个*12月=12个文件）

上面的时间策略需要根据数据库的安全级别、磁盘空间、业务特性等情况共同解决的，你可以根据的实际情况进行修改和调整；

4.下面的Figure3、Figure4、Figure5、Figure6是具体设置的效果图：

（Figure3：

子计划Full）

（Figure4：

子计划Diff）

（Figure5：

子计划Log）

（Figure6：

子计划Save）

5.保存维护计划后，在SQLServer代理的作业中会生成Figure7的作业列表：

（Figure7：

Job列表）

6.执行一段时间后，在各个文件夹中会生成一系列的文件：

（Figure8：

Full文件）

（Figure9：

Diff文件）

（Figure10：

Log文件）

（Figure11：

Save文件）

7.有了这些文件之后，我们可以虽然还原到某个时间点上的数据，数据的差异为一个小时，例如你是在2012-12-11的15:

48:

00分误操作了数据库，我能回滚到最接近这个时间的是：

2012-12-11的15:

00:

00

（Figure12：

还原完整备份文件）

（Figure13：

还原差异、日志备份文件）

在使用SSMS的还原的时候，系统会帮你找到最近的备份文件，并且进行了勾选，要完成上面的还原，根据我们之前的备份计划，我们需要文件：

1）完整备份文件：

Barfoo.Medusa_backup_2012_12_09_020005_0612500.bak

2）差异备份文件：

Barfoo.Medusa_backup_2012_12_11_020005_8354977.bak

3）日志备份文件：

Barfoo.Medusa_backup_2012_12_11_080005_4808937.trn

Barfoo.Medusa_backup_2012_12_11_090005_4816881.trn

Barfoo.Medusa_backup_2012_12_11_100005_6543553.trn

Barfoo.Medusa_backup_2012_12_11_110005_7645233.trn

Barfoo.Medusa_backup_2012_12_11_120005_8434417.trn

Barfoo.Medusa_backup_2012_12_11_130004_9379977.trn

Barfoo.Medusa_backup_2012_12_11_140005_2044137.trn

Barfoo.Medusa_backup_2012_12_11_150005_2208329.trn

还原的步骤就是先还原完整备份文件，接着还原差异备份文件，再依次执行日志备份文件，这样就能回滚到你想要的时间点：

2012-12-11的15:

00

4.2、注意事项

1.备份计划中“每天在8:

00:

00和23:

59:

59之间、每1小时执行数据库的日志备份”很多人会问：

这中间相差8个小时的数据不做日志备份呢？

其实我是这样考虑的，在凌晨的时候，大部分的情况这段时间是在跑定时器处理数据，如果不是程序有BUG，一般不会造成数据错误需要恢复，而且这个时候用户操作页面也不频繁（个人认为更多数据误操作是因为人为操作不当引起的），你可以根据自己的业务情况做些调整；

2.备份计划中“每个月的最后一个星期日的1:

00:

00执行数据库的完整备份”，这样做的目的是尽量不影响全备份又能保留一份完整的备份文件，如果需要更加严谨一点，你可以把它换成“仅复制备份（COPY_ONLY）”，这需要在维护计划中使用T-SQL进行备份，这就可以完全不影响日志链；

3. 维护计划的可以通过T-SQL脚本生成嘛？

sp_add_maintenance_plan

sp_delete_maintenance_plan_db

sp_add_maintenance_plan_db

sp_delete_maintenance_plan_job

sp_add_maintenance_plan_job

sp_help_maintenance_plan

sp_delete_maintenance_plan

 5、SQLServer数据库最小宕机迁移方案

5.1、目的

在做SQLServer数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服务器，并且要求最小宕机时间的时候，我们有没什么方案可以做到这些要求呢？

在这里我们假设这两台机器并不是在一个机房上，这样看起来我们的解决方案才更有意义，如果你那么好运这两台机器在同一个局域网，那么恭喜你，你可以多很多的方案可以做到。

5.2、分析与设计思路

其实我们假设的环境有两个特点：

第一个是数据库文件比较大；第二个就是我们的传送文件的速度可能会比较慢。

也许这传送速度我们是没有办法了，但是我们可以就从文件的大小这个问题出发，结合SQLServer的特性，这样就有了下面的解决方案了。

为了使宕机时间最短，我们这里使用了完整备份和差异备份来迁移数据库，在白天的时候对需要迁移的数据库进行一次完整备份（XXX_full.bak），并把备份文件拷贝（这里可以使用FTP软件进行断点续传）到目标服务器进行还原，等到下班时间之后再进行一次差异备份（XXX_diff.bak），再把这个差异备份拷贝到目标服务器，在完整还原的基础上再进行差异还原。

这里的宕机时间=差异备份时间+传送差异备份文件时间+还原差异备份文件时间，这宕机时间是不是让你感觉这时间很短呢？

5.3、参考脚本

注意修改下面脚本中数据库的名称，还有绝对路径。

--1：

完整备份

declare@dbnamevarchar（100）

declare@sqlnvarchar（max）

set@dbname='DataBaseName'

set@sql='

--'+@dbname+'_full

BACKUPDATABASE['+@dbname+']

TODISK=''D:

\DBBackup\'+@dbname+'_full.bak''

WITHNOFORMAT,NOINIT,NAME='''+@dbname+'-完整数据库备份'',

SKIP,NOREWIND,NOUNLOAD,STATS=10

GO'

print@sql

--生成的SQL

--DataBaseName_full

BACKUPDATABASE[DataBaseName]

TODISK='D:

\DBBackup\DataBaseName_full.bak'

WITHNOFORMAT,NOINIT,NAME='DataBaseName-完整数据库备份',

SKIP,NOREWIND,NOUNLOAD,STATS=10

GO 

--2：

完整备份还原

declare@dbnamevarchar（100）

declare@sqlnvarchar（max）

set@dbname='DataBaseName'

set@sql='

--RESTORE'+@dbname+'_full

RESTOREDATABASE['+@dbname+']

FROMDISK=''D:

\DBBackup\'+@dbname+'_full.bak''WITHFILE=1,

MOVEN''DataBase_Name''TON''D:

\DataBase\'+@dbname+'.mdf'',

MOVEN''DataBase_Name_log''TON''D:

\DataBase\'+@dbname+'_log.ldf'',

NORECOVERY,NOUNLOAD,REPLACE,STATS=10

GO'

print@sql

--生成的SQL

--RESTOREDataBaseName_full

RESTOREDATABASE[DataBaseName]

FROMDISK='D:

\DBBackup\DataBaseName_full.bak'WITHFILE=1,

MOVEN'DataBase_Name'TON'D:

\DataBase\DataBaseName.mdf',

MOVEN'DataBase_Name_log'TON'D:

\DataBase\DataBaseName_log.ldf',

NORECOVERY,NOUNLOAD,REPLACE,STATS=10

GO

--3：

差异备份

declare@dbnamevarchar（100）

declare@sqlnvarchar（max）

set@dbname='DataBaseName'

set@sql='

--'+@dbname+'_diff

BACKUPDATABASE['+@dbname+']

TODISK=N''D:

\DBBackup\'+@dbname+'_diff.bak''

WITHDIFFERENTIAL,NOFORMAT,NOINIT,NAME=N'''+@dbname+'-差异数据库备份'',

SKIP,NOREWIND,NOUNLOAD,STATS=10

GO

'

print@sql

--生成的SQL

--DataBaseName_diff

BACKUPDATABASE[DataBaseName]

TODISK=N'D:

\DBBackup\DataBaseName_diff.bak'

WITHDIFFERENTIAL,NOFORMAT,NOINIT,NAME=N'DataBaseName-差异数据库备份',

SKIP,NOREWIND,NOUNLOAD,STATS=10

GO

--4：

差异备份还原

declare@dbnamevarchar（100）

declare@sqlnvarchar（max）

set@dbname='DataBaseName'

set@sql='

--RESTORE'+@dbname+'_full

RESTOREDATABASE['+@dbname+']

FROMDISK=''D:

\DBBackup\'+@dbname+'_diff.bak''WITHFILE=1,

NOUNLOAD,STATS=10

GO'

print@sql

--生成的SQL

--RESTOREDataBaseName_full

RESTOREDATABASE[DataBaseName]

FROMDISK='D:

\DBBackup\DataBaseName_diff.bak'WITHFILE=1,

NOUNLOAD,STATS=10

GO