SQL Server 数据库的备份仔细讲解及注意事项

SQL Server 备份

前言

为什么要备份？理由很简单——为了还原/恢复。当然，如果不备份，还可以通过磁盘恢复来找回丢失的文件，不过SQL Server很生气，后果很严重。到时候你就知道为什么先叫你备份一次再开始看文章了。∩__∩。本系列将介绍SQL Server所有可用的备份还原功能，并尽可能用实例说话。

什么是备份？SQL Server基于Windows，以文件形式存放资料，所以备份就是Windows上SQL Server相关文件的一个某个时间点的副本。根据备份类型的不同，副本的种类和内容也有不同。

备份类型有哪些？SQL Server 目前版本中，可用的备份类型有：完整数据库备份、差异数据库备份、事务日志备份（后称日志备份）、文件和文件组备份、部分备份，根据SQL Server版本不同，有些备份类型不支持，另外根据恢复模式的不同，某些备份类型也不支持。

什么是恢复模式？

很多人只把关注点放在备份上面，而没有在意恢复模式，其实所有的备份都应该从恢复模式作为切入点。恢复模式实际上是一个控制备份还原的行为的数据库级别选项。SQL Server 在当前所有发布版本中只有三种恢复模式：简单恢复模式（后面简称简单模式），大容量日志恢复模式（后面简称大容量模式），完整恢复模式（后称完整模式）。

本文从恢复模式开始，提醒一下，绝大部分的专业属于都会陆续解释，如果读者有不明白，可以继续往下看或者上网搜索：

1.简单模式，Simple recovery model：某些操作可以被最小日志化。这种模式下，不支持日志备份、时间点恢复和页恢复。且文件恢复功能仅限于次要数据文件中的只读文件。

2.大容量日志模式，Bulk-logged recovery model：和完整模式类似，有时候可以理解为完整模式于简单模式的过渡模式。这种模式对某些大容量操作进行最小日志化，支持完整备份中的备份还原策略，但是由于某些操作被最小日志化，所以不能保证时间点恢复。

3.完整模式，Full recovery model： 在这个模式下，所以操作都被完整记录下来，并且支持所有类型的备份还原策略。

默认情况下，新库会继承Model库的配置，包括恢复模式，也就是FULL模式。可以在创建或日常使用过程中修改，并且不需要重启服务。恢复模式最重要的区别在于对待日志的行为。

简单模式：

是三种模式中最容易管理的，可以进行完整，差异和文件备份，但是不能做日志备份。在这种模式下，每当Checkpoint 进程发生时，会自动把日志文件中不活动的日志（在日志备份一文介绍）写入数据文件，写入后，对应的日志文件中的空间就可供新事务使用，注意这种空间重用或者截断并不自动减少日志文件的物理大小，如果需要减少空间，需要使用DBCC SHRINKFILE/DATABASE等命令实现。让日志空间重用的过程叫做截断。在简单模式下这个过程称为自动截断(auto-truncate)。在这种模式下，日志通常不需要管理，但是对于单个的大事务，日志文件可能会增长得很快，这种情况下最好把批处理降为小的批。简单模式最主要的限制是不能进行日志备份，也就是说无法进行时间点还原。在一些测试，开发或者SLA要求不严格的环境下，可以使用这种模式。

完整模式：

这种模式下，所有数据库操作都被完整地记录在日志中，2008出现某些操作在这种模式下也还是最小化日志。并且不是自动截断。它支持任何备份还原策略，特别是时间点还原，在日志还原一章介绍。即使发生Checkpoint ，不活动的事务也不会截断到数据文件中。唯一能控制日志文件的只有日志备份，所以这种模式下日志备份极其重要，一方面提供时间点还原，另外一方面控制日志文件大小。

日志文件会完整保存自上一次日志备份后的事务。使用copy_only或者no_truncate选项均不会截断日志。

大容量日志：

这种模式是最少用到的，某些操作会被最小日志化，包含：

• 使用bcp进行导入
• bulk
• insertinsert
• select *from openrowset(bulk )
• select into
• 使用writetext/updatetext插入或附加数据
• 重建索引

在这种模式下，会用bitmap image记录发生最小日志化的区。如果数据库故障导致数据文件不了用，并且日志尾部包含最小化日志，不能做日志尾部备份，因为这个操作需要访问数据文件中数据修改后的区。这种模式适用于大容量操作，但是如果事务包含最小化日志，则不能进行时间点还原，只能还原到之前。

恢复模式扩展说明：

如上所说，恢复模式是数据库级别的配置项，在创建过程中及后续使用中均可修改，但是由于种种原因，尽量在规划阶段就做好配置，并且在创建过程中明确指定。

这个选项主要用于决定数据库是否可以（或者需要）做日志备份？什么事务需要被记录？还有是否可以做其他类型的备份还原操作等。

简单模式：

某些操作能被最小化日志，这里要说明一下，很多人以为简单模式下“不记录日志”，其实这是很严重的误解，会导致后续使用的很多问题，无论任何恢复模式，都会记录日志，只是记录的形式和内容不同。在简单模式下，日志备份选项被禁用，带来的影响是不支持时间点还原、页还原，而文件还原功能仅限于READONLY文件组中的次要数据文件。

简单模式是最容易管理的恢复模式，在这种模式下，可以进行完整数据库备份、差异数据库备份和文件备份，但是不能进行日志备份。在日志备份一文会详细介绍，但是在这里要提一下，关于日志空间重用的问题，不管任何恢复模式，都会有一个系统进程在后台运行——CHECKPOINT，每当这个进程启动时，会把数据库的日志文件（通常就是LDF文件）中，非活动的事务写入数据文件，然后把这部分的空间标识为“可重用”，这个步骤称为日志截断，在简单模式下称为自动截断（auto-truncate），记住可重用不代表空间被清空，唯一可以清空LDF文件物理大小的操作是收缩数据库/文件操作。简单模式会自动执行这个截断操作，截断后，日志空间可被新事物重新使用，从宏观变现来说，就是LDF文件的物理大小不增加，或者增加缓慢，其实当使用简单模式，并且LDF合适的情况下，如果LDF物理大小还在增长，可能就需要引起注意。

由于日志的自动截断，导致简单模式下无法进行时间点恢复，也无法进行日志备份。但是对于对数据要求不高的系统，或者SLA（在还原基础一文中介绍）没有什么特殊要求的环境，可以使用这种模式，可以最大限度减少对日志的管理。但是不是意味着使用了这种模式，就不用管理日志了，对于一些大规模、长时间运行的批处理，会引起大量的活动事务，此时LDF文件依旧会迅速增长，引起一些潜在的问题。对此，尽可能把批处理拆分为多个、短事务。

简单来说，这种模式的优缺点：

优点：

易于管理，大部分情况下不需要

缺点：

• 不能进行事务日志备份，无法时间点还原
• 数据丢失的风险增大

选择依据：根据业务需要选择，对于非常重要的数据库，无论当前数据库大小，都不要使用这种模式，详细内容参考还原基础中SLA的内容。

完整模式：

完整模式很多概念都是相对于简单模式来说的，这种模式下，所有操作被完整地记录在事务日志文件中，并且不会发生自动截断（除了数据库完全没做过最少一次完整备份），事务日志只有在事务日志备份发生时，才会截断到数据文件，并且使对应部分可用。这种模式能够执行所有类型的备份还原选项，特别是可以进行时间点恢复，保证数据接近0丢失。这是几乎所有正式环境（也称生产环境）使用的恢复模式。

优点：

• 能够完整记录数据库操作
• 进行时间点恢复，保证数据尽可能0丢失

缺点：

• 需要严格管理事务日志文件
• 数据库规模可能会变得难以控制

大容量日志模式：

这是用的最少的恢复模式，读者不要给名字忽悠了，见过很多人在进行大容量操作时切换到这种模式，然后操作完再切换回来，这种操作其实比较危险。不建议使用。另外，它支持日志备份，能进行一定程度的时间点恢复。除了前面提到的可最小化日志的操作，其日常使用和管理与完整模式无疑。可以理解为是完整模式和简单模式的过渡。

缺点：

如果数据文件突然变得不可用，并且日志尾部包含了大容量日志模式下进行的最小化日志操作，那么不可能进行日志尾部备份，因为这种备份要求访问数据修改所发生的区，而这个区在最小化日志操作中仅记录“发生了操作”，而没有完整地记录操作内容。导致无法进行时间点还原，存在一定的数据丢失风险。做好事务管理的话，其实这种模式基本上没什么存在的价值。

备份成份：

现在来说说一个备份会包含什么内容，很多人以为，特别是完整数据库备份，就是把所有东西都备份，其实他们被名字迷惑了。在介绍备份成份前，先介绍SQL Server的数据库成份，SQL Server数据库是一系列基于Windows的文件，最简单的模式包含一个数据文件（默认后缀名为MDF）和一个日志文件（默认后缀名为LDF），后缀名能改，但是没有任何理由去改。后果很严重…。这两个文件在创建数据库时就自动创建，在后续运行当中，可能会创建多个数据文件（默认后缀名为ndf），多个日志文件（大部分情况下没必要，在日志备份一文介绍），还有一些文件组，每个文件组包含若干个文件。

数据文件：

数据文件是用于存储系统及用户数据及对象，简单来说，就是数据、表、视图、存储过程、触发器等等。除此之外，还包含权限信息。每个数据库最少要有一个数据文件，默认为主数据文件，primary data file，默认后缀名为.MDF。存储在主文件组（primary Filegroup中），如果需要新加文件，这些文件就是次要数据文件（虽然名字为次要，但是一点都不次要…），默认后缀名为.NDF。

主数据文件包含：所有系统对象和数据、默认情况下所有用户自定义的对象和数据。还有其他次要数据文件的地址。

文件组：

文件组是文件的一个逻辑集合，它可以包含一个或者多个数据文件，默认创建数据库时就会创建一个primary 文件组，存放primary数据文件。这个同时是default文件组，所有数据都会存放到这里，除非额外指定，default文件组可以改，前提是有两个或以上的文件组，这样可以把数据强制写到别的文件组中，有时候通过这种方式可以缓解磁盘的压力。另外primary文件组还存了其他所有文件组的路径。

对于多个文件组的数据库，可以进行文件组备份，这种方式对于超大型数据库（VLDB）非常有效，因为据我工作经验，即使一个150G的库做一个完整备份，也往往要进行20分钟左右，如果是150T的库，恐怕几个小时都搞不定，这时候，文件组备份就起到很重要的作用，把文件组控制在一定的大小，然后每次备份只对单独文件组进行，这样可以把一个连续的备份操作拆分为很多小操作。另外，文件组可以设为只读（read-only），这样可以在纯读操作中，减少锁和等待的产生，对性能方面有一定程度上的帮助。对于文件组配置放在其他章节，这里不累赘。

需要提醒的是，文件组带来性能方面的改进同时，也带来了管理方面复杂度的提升。所以需要慎重考虑。

事务日志：

这部分也有单独的介绍，这里只做简介，所有SQLServer数据库、所有恢复模式下，都有最少一个事务日志文件。虽然后面有专门的文章介绍，但是这里要不厌其烦地提醒，别因为任何模式、或者LDF文件太大就删除LDF让SQLServer，最严重的情况是会导致你的数据库无法使用。

备份类型：

目前微软已发布的SQLServer版本中，支持以下类型的备份：完整数据库备份、差异数据库备份、事务日志备份（后称日志备份）、文件和文件组备份、部分备份，但是如前面所说，根据SQL Server版本不同，有些备份类型不支持，另外根据恢复模式的不同，某些备份类型也不支持。数据文件、文件组及日志文件组成了SQL Server数据库，并且成为了各种备份类型的对象。下面简介一下各种备份类型：

数据库备份：把主数据文件和次要数据文件（如果有）上面的数据和对象存入备份文件中，这类细分为：

• 完整数据库备份：备份特定数据库的所有文件的所有数据和对象，还有足以用于在故障时恢复数据库到一致性状态的日志部分。
• 差异数据库备份：备份特定数据库上自最近一次完整数据库备份之后发生修改的所有数据文件的数据和对象。事务日志备份：把特定数据库自上一次日志备份后写入LDF文件的日志记录写入备份文件。

文件备份：把数据文件或者文件组中的数据及对象写入备份文件，可以细分为：

• 完整文件备份：备份在特定数据文件或文件组上的所有数据和对象。
• 差异文件备份：备份从上一次完整文件备份后特定数据文件或文件组中修改的数据和对象。
• 部分备份（完整部分备份）：备份数据库中除只读文件/文件组外（除非特殊指定）的所有可写部分。
• 差异部分备份：备份自上一次完整部分备份后发生变更的数据和对象。

再次说明，这些备份类型不是总是可用的，有些先决条件，特别是恢复模式，本系列将逐步演示这些操作。

备份需要考虑的因素：

备份时需要考虑以下几个因素，不能认为备份是简单操作，作为任何数据库管理（包括专业DBA或者兼职管理人员），备份都是第一要务，所以要认真对待：

• SLA
• 备份存放位置
• 备份周期及备份类型组合
• 备份文件存放周期
• 执行备份的工具
• 对性能的影响

这些部分将在后续陆续介绍。

What's the next?

1、准备环境，本系列主要使用Windows Server 2012 R2+SQL Server 2008 R2企业版+AdventureWorks 2008 R2数据库及为了演示而额外创建的一些数据库。

2、下文将演示完整数据库备份，需要注意，是完整数据库备份，而不是完整备份，虽然大部分情况下这是等价的，但是完整备份实际上包含完整文件备份，为了减少误解，这里需要说明是数据库备份。

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！