SQL SERVER 备份还原策略|江阴雨辰互联

2023年7月30日发(作者：)

SQL Server 备份与还原策略

一、概述

数据库在运行过程中，因为各种各样的原因会导致数据库损坏。常见的导致数据库损坏的原因有：数据库存储介质的损坏导致数据库不能正常启动、数据库服务器突然停机或重启导致数据库系统文件损坏或数据不完整、对操作系统不正常的操作导致数据文件损坏或丢失、数据库管理员误操作导致数据库被删除。数据库损坏后，要使用数据库的备份对数据库进行还原。为了能完整的恢复数据库，备份和还原需要一个策略。一个考虑了特定的业务需求的设计良好的备份策略会最大化数据的可用性，最小化数据的丢失。

二、 SQL Server备份恢复策略

备份与还原策略包含备份和还原两部分。备份部分定义备份的类型和备份频率、备份到的硬件的环境、备份的验证、备份位置和备份介质如何进行存储（包括安全性的考虑）。还原部分定义还原到的对象和怎样进行还原来达到数据库可以正常使用和最小化数据丢失的目标。

SQLSERVER 的备份类型

A．数据库备份(Database Backups)

数据库备份是指对数据库的完整备份，包括所有的数据以及数据库对象。实际上备份数据库过程就是首先将事务日志写到磁盘上，然后根据事务创建相同的数据库和数据库对象以及拷贝数据的过程。由于是对数据库的完全备份，所以这种备份类型不仅速度较慢，而且将占用大量磁盘空间。

B．事务日志备份(Transaction Log Backup)

事务日志备份是指对数据库发生的事务进行备份，包括从上次进行事务日志备份、差异备份和数据库完全备份之后，所有已经完成的事务。

C．差异备份(Differential Database Backups)

差异备份是指将最近一次数据库备份以来发生的数据变化备份起来，因此差异备份实际上是一种增量数据库备份。与完整数据库备份相比，差异备份由于备份的数据量较小，所以备份和恢复所用的时间较短。但是它却无法像事务日志备份那样提供到失败点的无数据损失备份。

D．文件和文件组备份(File and File Group Backup)

文件或文件组备份是指对数据库文件或文件夹进行备份，但其不像完整的数据库备份那样同时也进行事务日志备份。使用该备份方法可提高数据库恢复的速度，因为其仅对遭到破坏的文件或文件组进行恢复

设计备份恢复策略，考虑的因素

设计备份恢复策略，需要考虑很多因素的影响，下面是主要的影响因素：

1、恢复模式对备份恢复策略的影响：恢复模式是控制数据库事务日志管理的数据库的一个属性，恢复模式有三种：简单恢复、完整恢复、大容量日志恢复，通常采用简单恢复模式和完整恢复模式。简单恢复模式不支持事务日志的备份，使用该模式进行备份，数据库只能恢复到备份的那个时间点。完整恢复模式支持事务日志的备份，使用该模式，数据库可以恢复到任意时间点，例如可以恢复到数据库发生异常的时间点。

2、在数据库发生异常后，进行恢复时，业务上对丢失数据的要求程度。如果业务上可以承受当天业务数据的丢失，那么就可以采用1天备份1次的策略；如果业务上要求所有的数据都不能丢失，那么肯定要进行事务日志的备份。

3、数据库大小对备份恢复策略的影响：数据库不大，1次完全备份耗用时间不长，可以使用完全备份；数据库很大，1次完全备份耗用时间很长，有可能影响正常的业务运行，那么就不能考虑完全备份，要进行差异备份。

4、资源的要求：硬件的限制、个性化的需求、存储备份介质的空间大小、存储介质的物理安全性等等。例如备份出的文件20G，公司要求保留1周的备份，存储备份文件的磁盘就需要20*7=140G的空间，管理员需要考虑存储备份文件的磁盘空间是否够用和每天的备份文件是否要存放到不同的磁盘上。

5、数据库发生异常后，业务上对恢复时间的要求。如果要求不能影响正常业务的运行，例如使用了生产系统，业务运行不能中断，那么就要考虑双机热备份的策略。备份策略的设计

根据业务需求针对数据库选择了恢复模式后，需要设计和形成一个对应的备份策略。好的备份策略依赖于不同的因素，下面是最重要的一些因素：

1．应用程序1天有多少小时在访问数据库？

如果有一个可预测的空闲时间段，建议在那个时间段安排数据库备份。在使用GS软件时，根据使用的模块大致可以确定备份时间。例如客户使用了基本财务模块，一般业务在晚上12点以后不会再发生，那么就可以考虑在晚上12点安排数据的备份。客户使用了物流、生产模块，业务随时在发生，但临晨3点左右业务发生最少，那么可以考虑在临晨3点进行数据库的备份。

2．数据库变化和更新的频率有多高？如果变化频繁，考虑下面的策略：

简单恢复模式下，在完整的数据库备份之间安排差异备份。差异备份只记录最后一次数据库完整备份后的数据变化。例如可以安排周日进行完全备份，周一到周六进行差异备份。

完整恢复模式下，应当频繁的进行日志备份，完整的备份之间安排差异备份。例如可以安排周日进行完全备份、周一到周六进行差异备份、每天间隔2个小时进行事务日志的备份。

3．发生变化的是数据库的一小部分还是一大部分?

一个大型数据库的变化集中在几个文件或文件组，可以使用部分备份或文件的备份。

GS软件，一般在创建数据库时，整个数据库都创建在了1个文件里，因此部分文件的备份没有多大意义，该因素可以忽略掉。

4．完整的数据库备份需要多大磁盘空间？

备份操作是将数据库里的数据拷贝到备份文件里。备份只针对数据库里的实际数据，不包含没有使用的空间。因此，备份后的文件通常比数据库要小。使用sp_spaceused 存储过程，可以估算数据库完整备份后的文件大小。

如果备份需要很大磁盘空间，备份时间很长，每天1次完全备份的策略就不能考虑了。可以考虑1周进行1次完全备份，两次完全备份之间每天进行差异备份。

5．数据库发生故障后，系统是否要求不能中断正常的业务运行？业务不能中断进行，就要考虑双机热备份策略。

常见的备份策略及优缺点

应用场景优点备份策略缺点

要求数据库不能太数据库占用空只需要保留最后一大，数据库出故障后间不是很大，1每天进行1次完全次的备份文件就可只能恢复到最近一次次完全备份时备份以，备份文件管理简备份时间点的数据。间不会超过2个单。业务数据会丢失一部小时

分

数据库占用空数据库出故障后，只间很大，1次完差异备份的文件不1周1次完全备份，能恢复到最近一次备全备份时间很会很大，备份时间不两次完全备份间每份时间点的数据，业长，还原时不需会很长，备份文件不天进行差异备份务数据会丢失一部要还原到某个多，容易管理。

分。

时间点

每天进行多次日志备数据库占用空份，对数据库运行性1周1次完全备份，间很大，1次完能会有影响。备份文两次完全备份间每数据库能还原到出全备份时间很件很多，不容易进行天进行差异备份，故障的时间点，业务长，还原时需要管理。数据库出故障每天每隔2小时进数据不会丢失

还原到某个时后，恢复需要时间，行事务日志的备份

间点影响业务系统的运行。

1台数据库服务器出故障后，系硬件投资大，不容易出故障时，业务不会双机热备统马上切换到进行部署，系统运行中断

另一台服务器，维护成本高

业务照常进行

推荐的备份方案

①、数据库出故障后，业务不能停止运行。请使用双机热备。

②、数据库出故障后，业务可以停止运行，但数据不能丢失，请使用：1周1次完全备份，两次完全备份间每天进行差异备份，每天每隔2小时进行事务日志的备份（出故障时，如果能进行事务日志的备份，请手工执行日志备份，否则数据会丢失一部分）。

③、数据库出故障后，可以丢失1天的数据。如果完全备份1次时间太长，请1周1次完全备份，两次完全备份间每天进行差异备份。1次完全备份时间不长，可以每天进行1次完全备份。

④、数据库完全备份后，想收缩日志文件占用空间。可以在完全备份后，进行日志截断及收缩数据库。

还原策略的设计

根据数据库出故障时，故障现象及故障原因的不同、备份方式的不同，还原方式都不一样，常见故障及还原方式如下：

1、数据库能正常启动，但运行完整性检查时（DBCC CHECKDB），发现数据库有问题，备份文件恢复，也存在数据库不完整的现象。

数据库和备份文件都存在数据不完整的现象，只能通过数据转入转出的方式来实现数据库的恢复。具体步骤：新建1个数据库（使用GS安装盘里的数据库创建工具），清空所有用户表里的数据，使用sqlserver提供的导出功能，将有问题的数据库里的数据导出到新建的数据库里。

2、数据库存储的磁盘完全损坏，文件不能恢复。

磁盘损坏后，要先看数据文件和日志文件是否能恢复（恢复工作要找专业的数据恢复公司），如果数据文件、日志文件都不能恢复，那只能从最近的备份文件还原。

3、数据库发生异常，检查数据库，发现日志文件发生异常，数据文件正常（查看数据文件异常，还是日志文件异常，可以从SQL SERVER的错误日志里发现，SQL SERVER的错误日志在Microsoft SQL ServerMSSQL.1MSSQLLOGERRORLOG 里面，可以使用记事本打开）。

数据文件正常，日志文件损坏或丢失的现象，可以通过日志文件的重新构造来解决。

4、数据文件损坏，日志文件正常。

根据最后一次备份的时间点，使用Log Explorer工具，将备份的时间开始到出错的时间点的所有的SQL语句转出成sql文件。使用备份文件恢复数据库到备份的时间点，将Log Explorer转出的SQL文件在恢复后的数据库里执行，数据就还原到出故障的时间点了。三、 SQL Server备份恢复操作

备份操作

1、1天1次完全备份

进入SQL SERVER管理器后，以sa身份登录系统，在对象资源管理器里，找到管理下的维护计划，右键选择维护计划向导，如下图：

在弹出的界面中，输入名称和描述，如下图：点右下角的更改按钮，弹出如下界面，执行频率选择每周，执行间隔选择周一：点确定按钮后，返回维护计划向导界面，点下一步按钮，弹出如下界面，请选择备份数据库（完整）：点下一步，弹出选择维护任务顺序界面如下图：因为只选择了一个任务，直接点下一步按钮，在弹出的界面中选择要备份的数据库：选择好数据库后，点确定按钮，选择跨一个或多个文件备份数据库，如下图：点右边的添加按钮，弹出选择备份目标界面，如下图：选择要备份到的文件名，如下图：选择好文件（如果文件不存在，请输入要备份到的文件名）后，点确定按钮，返回选择备份目标界面，再点确定按钮，返回配置任务界面，在如果备份文件存在下拉列表里，选择覆盖，如下图：点下一步按钮，弹出选择报告选项界面如下图：直接点下一步按钮，可以查看一下设的备份计划是否正确，如下图：点完成按钮，系统会自动创建备份计划，弹出的备份进度见下图：点关闭，每周一的备份计划设置完成。

重复上述步骤，备份时间选择每周周二，备份文件选择周二文件夹里的文件，如下图：

这样，周二的备份计划就做好了。

其他几天的备份计划，按照上述步骤重复设置。

2、周末完全备份，每天增量备份

进入SQL SERVER管理器后，以sa身份登录系统，在对象资源管理器里，找到管理下的维护计划，右键选择维护计划向导，如下图：

在弹出的界面中，输入名称和描述，如下图：点右下角的更改按钮，弹出如下界面，执行频率选择每周，执行间隔选择周一：点确定按钮后，返回维护计划向导界面，点下一步按钮，弹出如下界面，请选择备份数据库（差异）：点下一步，弹出选择维护任务顺序界面如下图：因为只选择了一个任务，直接点下一步按钮，在弹出的界面中选择要备份的数据库：选择好数据库后，点确定按钮，选择跨一个或多个文件备份数据库，如下图：点右边的添加按钮，弹出选择备份目标界面，如下图：选择要备份到的文件名，如下图：选择好文件（如果文件不存在，请输入要备份到的文件名）后，点确定按钮，返回选择备份目标界面，再点确定按钮，返回配置任务界面，在如果备份文件存在下拉列表里，选择覆盖，如下图：点下一步按钮，弹出选择报告选项界面如下图：直接点下一步按钮，可以查看一下设的备份计划是否正确，如下图：点完成按钮，系统会自动创建备份计划，弹出的备份进度见下图：点关闭，每周一的备份计划设置完成。

重复上述步骤，备份时间选择每周周二，备份文件选择周二文件夹里的文件，如下图：

这样，周二的备份计划就做好了。周三、周四、周五、周六的备份计划和上述步骤一样。

星期天的备份计划除了备份时间和备份文件不一样外，在选择维护任务时，请选择备份数据库（完整），如下图：其他的设置都一样。

3、周末完全备份、每天增量备份、2个小时1次日志备份

周末完全备份、每天增量备份。

每天日志备份操作如下图：在弹出的界面中，输入名称和描述，如下图：

点右下角的更改按钮，弹出如下界面，执行频率选择每天，执行间隔选择2小时：点确定按钮后，返回维护计划向导界面，点下一步按钮，弹出如下界面，请选择备份数据库（事务日志）：点下一步，弹出选择维护任务顺序界面如下图：因为只选择了一个任务，直接点下一步按钮，在弹出的界面中选择要备份的数据库：选择好数据库后，点确定按钮，选择要存放的文件夹如下图：点下一步按钮，弹出选择报告选项界面如下图：直接点下一步按钮，可以查看一下设的备份计划是否正确，如下图：点完成按钮，系统会自动创建备份计划，弹出的备份进度见下图： 4、双机热备概要介绍

双机热备这一概念包括了广义与狭义两种意义。

从广义上讲，就是对于重要的服务，使用两台服务器，互相备份，共同执行同一服务。当一台服务器出现故障时，可以由另一台服务器承担服务任务，从而在不需要人工干预的情况下，自动保证系统能持续提供服务。

双机热备由备用的服务器解决了在主服务器故障时服务不中断的问题。但在实际应用中，可能会出现多台服务器的情况，即服务器集群。

双机热备一般情况下需要有共享的存储设备。但某些情况下也可以使用两台独立的服务器。

实现双机热备，需要通过专业的集群软件或双机软件。

从狭义上讲，双机热备特指基于active/standby方式的服务器热备。服务器数据包括数据库数据同时往两台或多台服务器写，或者使用一个共享的存储设备。在同一时间内只有一台服务器运行。当其中运行着的一台服务器出现故障无法启动时，另一台备份服务器会通过软件诊测（一般是通过心跳诊断）将 standby机器激活，保证应用在短时间内完全恢复正常使用。

5、完全备份后，收缩日志文件占用空间。

以sa口令登陆，在对象浏览器里找到管理—维护计划，找到完全备份的计划，如下图：

双击该计划，打开计划的详细属性，如下图：

在中间的工具箱里，找到 “执行 T-SQL 语句”任务，拖动右下角的图形里，如下图：双击“执行 T-SQL 语句”任务的图标，弹出了sql语句输入窗口，请输入

BACKUP LOG dbname WITH NO_LOG

dbcc SHRINKDATABASE('dbname')

如下图：

点确定按钮，返回任务维护界面，将两个任务连接起来（使用鼠标将一个任务的箭头拖到另一个任务），如下图：上述操作做完，点工具栏上的保存按钮，将维护计划进行保存。

恢复操作

1、从备份文件恢复数据库

进入SQL SERVER管理器后，以sa身份登录系统，在对象资源管理器里，选择要还原的数据库，右键找到任务—还原—数据库，如下图：

进入界面如下图：