SQL Server 数据库的备份详细介绍及注意事项-巨人网络通讯

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数据库，并且成为了各种备份类型的对象。下面简介一下各种备份类型：

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