SQLSERVER的备份和恢复策略

SQL SERVER的备份和灾难恢复策略

陈 克 江苏省常熟市广播电视台网络中心

摘要：阐述SQL SERVER的数据安全性的一些基本概念，针对故障导致数据丢失的不同情况制定相应恢复策略。

关键词： 数据库 备份 故障 恢复策略

SQL SERVER作为Microsoft主要的数据库产品，以其强大的功能、可靠性和易管理性在包括广电行业的各企事业单位中得到广泛使用，各地广电部门的用户管理系统和地理信息系统等数据库应用中大量使用着SQL SERVER数据库系统，数据的重要性不言而喻，因此作为广电行业的运行维护人员，对于SQL SERVER系统应该有充分的了解。其中最重要的部分就是要了解数据库系统的日常维护和出现故障之后数据的恢复过程。

1 故障原因

计算机系统在长期运行中必定会出现种种故障，诸如：

硬件故障：计算机的硬件产生故障导致计算机不能正常工作

软件故障：计算机软件存在的错误和漏洞导致的故障

使用错误：客户的误操作

病毒和恶意破坏：由于计算机感染病毒或受到恶意攻击引起的故障

还有其它很多因素都可能造成计算机系统的崩溃或数据的丢失。因此必须事先制定行之有效的备份和恢复策略，才能在最短的时间恢复正常工作，将损失降低到最小。

2 有关数据恢复的基本概念

2.1 数据库的分类：

SQL SERVER典型安装后，系统自动生成多个数据库：Master，Model，Msdb，Northwind，Pubs，Tempdb。

其中Northwind和Pubs是微软提供的示例数据库，与用户定义数据库同属于用户数据库；Master、Model、Msdb、 Tempdb等4个数据库是SQL SERVER的系统数据库，其作用分别如下：

(1) Master数据库

Master数据库记录了SQL SERVER所有的服务器级系统信息，所有注册帐户和密码，以及所有的系统设置信息以及用户定义数据库的存储位置和初始化信息。

(2) Tempdb数据库

Tempdb记录了所有的临时表、临时数据和临时创建的存储过程。Tempdb数据库是一个全局资源，没有专门的权限限制，允许每个可以连上SQL SERVER服务器的用户使用。

在Tempdb数据库存放的数据信息都是临时的，每当连接断开时，所有的临时表和临时存储过程都将自动丢弃。每次系统启动时SQL SERVER都会根据Model数据库重新创建Tempdb数据库。

(3) Model数据库

Model数据库是用户建立新数据库的模板，它包含了将复制到每个用户数据库中去的系统表。当创建数据库的语句CREATE DATABASE执行时，服务器将Model数据库中的所有

对象复制到新数据库中。

(4) Msdb数据库

Msdb数据库用来保存数据库备份、SQL Agent信息、DTS程序包、SQL SERVER任务等信息，以及日志转移等复制信息。该数据库常被用来通过调度任务排除故障。

另外，当SQL SERVER被配置为复制分发时，Distribution数据库会被添加系统里。这个数据库用来保存历史和快照、合并和事务复制等的元数据。

2.2 数据库恢复模型

在数据库属性中可以选择3种恢复模型（见图1）。

图1 故障恢复模型

(1) 简单恢复模型

只保存临时性的日志，产生的事务会循环使用事务日志的空间，备份时不能进行事务日志备份。这种恢复模型的优点是事务日志较小，拥有较高的系统性能，但恢复时可能丢失上次完全或差异备份以来所有事务日志。

(2) 完全恢复模型

所有事务包括系统操作（如创建索引）都会完整的记录于事务日志中，故障恢复时可以将数据库每个提交的事务都还原到故障发生前特定的时间点。此种模型需要较大的磁盘空间，某些操作可能因此变慢。

(3) 大容量日志记录恢复模型

类似于完全恢复模型，但不将大容量日志记录操作（BLOB操作、Select* into table

操作等）作为事务进行日志备份，因此能提高大容量复制等操作的性能。发生故障后数据丢失程度要比完全恢复模型严重。如果系统性能允许，应尽可能使用完全恢复模型。

2.2 备份选项：

SQL SERVER提供了4种备份选项（见图2），选项的可用性由数据库恢复模型决定。

图2 数据库备份选项

(1) 完全备份

完整备份此时间点所有的数据。

(2) 差异备份

保存上一次完整备份之后改动的所有数据页。

(3) 事务日志备份

备份日志中的所有事务。

(4) 文件和文件组

备份文件或文件组中所有数据。

3 典型备份策略

3.1 简单恢复模型下的典型备份计划

每周执行1次完全备份，每个工作日下班后执行差异备份（见表1）。

时间

18:00

星期日

完全

星期一

差异

星期二

差异

星期三

差异

星期四

差异

星期五

差异

星期六

差异

表1 简单备份计划

简单备份计划适用于对实时数据敏感性不高，数据更新量不大的数据库系统，对计算机系统资源要求较低。由于使用简单恢复模型在发生故障时可能会丢失较多数据和全部日志，所以不推荐在有线电视用户管理系统等重要数据库系统中使用。

3.2 完全恢复模型下的典型备份计划

每周执行2次完全备份，其余每天下班后进行差异备份，白天工作时间每2小时（或更短周期）进行日志备份（见表2）。

时间

10:00

12:00

14:00

16:00

18:00

星期日

日志

日志

日志

日志

完全

星期一

日志

日志

日志

日志

差异

星期二

日志

日志

日志

日志

差异

星期三

日志

日志

日志

日志

完全

星期四

日志

日志

日志

日志

差异

星期五

日志

日志

日志

日志

差异

星期六

日志

日志

日志

日志

差异

表2 完全备份计划

完全备份计划尽可能的保证了数据的正确性，虽然占用较多的磁盘空间和系统资源，但相对于重要数据的安全是完全值得的。

3.3 大容量日志恢复模型下的备份计划与完全备份计划相似，如果数据库应用中没有大容量数据操作，则大容量日志恢复模型可以替代完全恢复模型。

3.4 除定期备份用户数据库外，系统数据库也应根据数据库系统结构和内容上的变化进行不定期的完全备份：

(1) 以下情况需备份Master数据库：创建、删除数据库；登录帐户的增加和改变；服务器或数据库配置选项等。

(2) 以下情况需备份Msdb数据库：备份；保存DTS包；SQL SERVER AGENT计划调度等。

(3) Model数据库在修改过之后也应完全备份。

补充：SQL Server数据库备份有两种方式，一种是上文所述使用企业管理器（或自行编写代码）对数据库文件进行常规备份，另外一种就是直接拷贝数据库文件mdf和日志文件ldf作为备份（备份时需要停止数据库服务，拷贝完成后重新启动服务），建议只作为补充手段而不能作为主要备份方式。详细的数据库备份策略应根据实际的数据库重要性、更新频率和时段以及可用的软硬件资源来确定，各单位可因地制宜实施相关策略。

4 灾难恢复策略

当数据库出现明显的软硬件损坏、或者根据提示检测出数据库已无法正常工作（如SQL

SERVER的企业管理器提示某数据库“置疑”），应按照事先部署的计划进行紧急处理。

首先，尽快修复硬件和操作系统故障（平时应储备备用零部件或服务器，操作系统所在分区应做镜像备份），然后针对具体的损坏情况实施恢复策略。

4.1 用户数据库在不同模型下的恢复方法：

(1) 按照简单恢复模型进行还原应遵循以下步骤：

① 使用最新的完全备份进行还原

② 使用最新的单个差异备份（如果有）进行还原

③ 数据库将恢复到最后一次差异备份时刻的状态，所有事务日志将丢失。

⑵ 按照完全恢复模型进行还原应遵循以下步骤：

① 备份当前事务日志

② 使用最新的完全备份进行还原

③ 使用最新的单个差异备份（如果有）进行还原

④ 还原最新的事务备份（完全或差异）

(3) 按大容量日志记录恢复模型恢复数据与完全恢复模型类似。

4.2 用户数据库的数据库文件恢复方法

(1) 如果没有常规备份文件而用户数据库文件mdf和日志文件ldf本身没有损坏，或者用户有最新的可接受的数据库文件备份，可以考虑直接使用mdf和ldf 文件恢复数据库。恢复步骤如下：

① 停止数据库服务（如果企业管理器中能看到原数据库，可先用“SP_DETACH_DB 数

据库名”命令卸载数据库再停止服务）

② 将数据库文件拷贝到原安装目录

③ 在企业管理器中执行“附加数据库”命令，选中数据库文件

④ 也可以用命令执行，例如：

SP_ATTACH_DB 'test','C:Program FilesMicrosoft SQL ServerMSSQLDatatest_','C:Program FilesMicrosoft SQL ServerMSSQLDatatest_'

SP_ATTACH_SINGLE_FILE_DB 'test','C:Program FilesMicrosoft SQL ServerMSSQLDatatest_'

⑤ 启动数据库服务

(2) 如果只有mdf文件的备份，ldf文件丢失，则可能无法恢复数据库。出现这种情况可尝试如下步骤：

① 新建同名数据库如test

② 删除日志文件test_

③ 将test数据库设置为脱机

④ 在查询分析器中以超级用户登入，执行以下存储过程

EXEC sp_detach_db @dbname = 'test'

EXEC sp_attach_single_file_db @dbname='test', @physname ='C:Program FilesMicrosoft

SQL ServerMSSQLDatatest_'

⑤ 将test数据库设置为联机

⑥ 刷新数据服务并检查数据是否正确

4.3 系统数据库恢复方法

(1) Master数据库

如果Master数据库损坏，SQL SERVER服务将无法启动，不能使用上述方法恢复。应遵循以下步骤进行恢复：

① 删除默认位于”C:Program FilesMicrosoft SQL ”的数据库文件

② 在”C:Program FilesMicrosoft SQL Server80toolsbinn”中找到并运行，将启动rebuild master程序，需要提供3个参数：要恢复的服务器名称、Master数据库原来的在安装程序中的位置（一般为x86data目录）、服务器的排序规则。将重建全部系统数据库以及Northwind、Pubs两个范例数据库，并重新配置服务器

③ 使用服务管理器启动服务

④ 以sa身份（密码为空）打开企业管理器，在“服务器属性”—“启动参数”中填写“ –m”，将服务器设置为单用户模式，防止其他用户访问数据库修改数据

⑤ 关闭企业管理器，停止并重新启动SQL SERVER服务

⑥ 由于单用户模式下不能使用企业管理器，应该使用查询分析器进行还原操作，命令

如下：

RESTORE DATABASE master

FROM

DISK=’d:masterbackup’

With file = 1

执行结果如下:

⑦ 将启动参数中的“ –m”去掉，恢复多用户状态

⑧ 停止并重新启动服务

(2) Msdb数据库

由于Msdb数据库保存了备份的历史记录，因此服务器不能得到正确备份信息，Msdb只能作为备份设备来还原。如果服务器完全损坏，就必须将所有数据建立到新服务器上，以下是必须遵循的步骤：

① 设置windows服务器，还原域登陆帐户以保证能通过windows验证

② 安装SQL SERVER服务器以及升级包

③ 将服务器设置为单用户模式，还原Master数据库

④ 还原Msdb数据库

⑤ 根据需要恢复Model数据库

⑥ 还原用户数据库

⑦ 将服务器恢复到多用户状态并重新启动

5 总结

数据的安全来自于平时有序、可靠的维护和备份工作，只有长期坚持不懈的进行数据的安全备份，才能在灾难发生时在最短的时间内正确恢复数据，保证业务的正常开展。如果条件允许，可考虑为重要数据库服务建立基于 Windows 2000 Advanced Server 和 SQL

SERVER 2000 的群集，多台服务器共同分担资源消耗，任何一台发生故障后其它服务器可继续提供服务使业务不会中断，整个过程无需人工干预，最大程度保证数据的安全可靠性。

参考文献：

1. Paul Nielsen 《Microsoft SQL Server 2000 Bile》

2. 孙炳旭，陈飞，梁茂盛 《SQL SERVER 7.0》