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数据库中的所有
(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任务等信息，以及日志转移等复制信息。该数据库常被用来通过调度任务排除故障。
(4) Msdb数据库
Msdb数据库用来保存数据库备份、SQL Agent信息、DTS程序包、SQL SERVER任务等信息，以及日志转移等复制信息。该数据库常被用来通过调度任务排除故障。
另外，当SQL SERVER被配置为复制分发时，Distribution数据库会被添加系统里。这个数据库用来保存历史和快照、合并和事务复制等的元数据。
2.2 数据库恢复模型
在数据库属性中可以选择3种恢复模型（见图1）。
图1 故障恢复模型
(1) 简单恢复模型
只保存临时性的日志，产生的事务会循环使用事务日志的空间，备份时不能进行事务日志备份。这种恢复模型的优点是事务日志较小，拥有较高的系统性能，但恢复时可能丢失上次完全或差异备份以来所有事务日志。
(2) 完全恢复模型
所有事务包括系统操作（如创建索引）都会完整的记录于事务日志中，故障恢复时可以将数据库每个提交的事务都还原到故障发生前特定的时间点。此种模型需要较大的磁盘空间，某些操作可能因此变慢。
(3) 大容量日志记录恢复模型
类似于完全恢复模型，但不将大容量日志记录操作（BLOB操作、Select* into table 操作等）作为事务进行日志备份，因此能提高大容量复制等操作的性能。发生故障后数据丢失程度要比完全恢复模型严重。如果系统性能允许，应尽可能使用完全恢复模型。
类似于完全恢复模型，但不将大容量日志记录操作（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 | 完全 | 差异 | 差异 | 完全 | 差异 | 差异 | 差异 |
| sqlserver备份表语句 |
表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 Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf','C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf'
SP_ATTACH_SINGLE_FILE_DB 'test','C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf'
SP_ATTACH_SINGLE_FILE_DB 'test','C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf'
⑤ 启动数据库服务
(2) 如果只有mdf文件的备份，ldf文件丢失，则可能无法恢复数据库。出现这种情况可尝试
如下步骤：
① 新建同名数据库如test
② 删除日志文件test_log.ldf
③ 将test数据库设置为脱机
④ 在查询分析器中以超级用户登入，执行以下存储过程
EXEC sp_detach_db @dbname = 'test'
EXEC sp_attach_single_file_db @dbname='test', @physname ='C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf'
⑤ 将test数据库设置为联机
⑥ 刷新数据服务并检查数据是否正确
4.3 系统数据库恢复方法
(1) Master数据库
如果Master数据库损坏，SQL SERVER服务将无法启动，不能使用上述方法恢复。应遵循以下步骤进行恢复：
① 删除默认位于”C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf”的数据库文件
② 在”C:\Program Files\Microsoft SQL Server\80\tools\binn”中到并运行，将启动rebuild master程序，需要提供3个参数：要恢复的服务器名称、Master数据库原来的在安装程序中的位置（一般为x86\data目录）、服务器的排序规则。将重建全部系统数据库以及Northwind、Pubs两个范例数据库，并重新配置服务器
③ 使用服务管理器启动服务
④ 以sa身份（密码为空）打开企业管理器，在“服务器属性”—“启动参数”中填写“ –m”，将服务器设置为单用户模式，防止其他用户访问数据库修改数据
⑤ 关闭企业管理器，停止并重新启动SQL SERVER服务
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论