1. 恢复模式

“恢复模式” 是一种数据库属性，它控制如何记录事务，事务日志是否需要（以及允许）进行备份，
以及可以使用哪些类型的还原操作。有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。数据库可以随时切换为其他恢复模式。 

1.1 简单恢复模式 Simple

1. Checkpoint with truncate log
2. 在Simple模式下，SQLServer会在每次checkpoint或backup之后自动截断log，也就是丢弃所有的inactive log records，仅保留用于实例启动时自动发生的instance recovery所需的少量log。
3. 这样做的好处是log文件非常小，不需要DBA去维护、备份log。
4. 但坏处也是显而易见的，就是一旦数据库出现异常，需要恢复时，最多只能恢复到上一次的备份，无法恢复到最近可用状态，因为log丢失了。
5. Simple模式主要用于非critical的业务，比如开发库和测试库。
6. 如果需要压缩数据库日志（Shrink语句），将数据库模式切换到简单恢复模式后压缩率才是最高的，如果你的数据库在完整恢复模式或大容量日志回复模式下采用日志压缩，压缩后的日志大小并不会很理想。

1.2 完整恢复模式 Full

1. Checkpoint without truncate log
2. 在Full模式下，SQL Server不主动截断log，只有备份log之后，才可以截断log，否则log文件会一直增大，直到撑爆硬盘，因此需要部署一个job定时备份log。
3. 好处是可以做point-in-time恢复，最大限度的保证数据不丢失，一般用于critical的业务环境里。
4. 缺点就是DBA需要维护log，增加人员成本（其实也就是多了定时备份log这项工作而已）。

1.3 大容量日志模式 Bulk-logged

1. Bulk-logged模式和full模式类似
2. 唯一的不同是针对以下Bulk操作，会产生尽量少的log： 1) Bulk load operations (bcp and BULK INSERT). 2) SELECT INTO. 3) Create/drop/rebuild index
3. 众所周知，通常bulk操作会产生大量的log，对SQL Server的性能有较大影响，bulk-logged模式的作用就在于降低这种性能影响，并防止log文件过分增长，它的问题是无法point-in-time恢复到包含bulk-logged record的这段时间。
4. Bulk-logged模式的最佳实践方案是在做bulk操作之前切换到bulk-logged，在bulk操作结束之后马上切换回full模式。

1.4 如何使用恢复模式

1.5 恢复模式切换建议

在完整恢复模式和大容量日志恢复模式之间切换后：
1. 完成大容量操作之后，立即切换回完整恢复模式。
2. 在从大容量日志恢复模式切换回完整恢复模式后，备份日志。(备份策略保持不变: 继续执行定期数据库备份、日志备份和差异备份。)

从简单恢复模式切换之后：
1. 切换到完整恢复模式或大容量日志恢复模式之后，立即进行完整数据库备份或差异数据库备份以启动日志链。(**到完整恢复模式或大容量日志恢复模式的切换仅在第一个数据备份之后才生效**。)
2. 计划安排定期日志备份并相应地更新还原计划。(**备份日志，如果不经常备份日志，事务日志可能会扩展直到占满磁盘空间**！)

切换到简单恢复模式之后：
1. 中断用于备份事务日志的所有计划作业。
2. 确保定期执行数据库备份。备份数据库对于保护数据和截断事务日志的不活动部分是基本操作。

2. 备份设备

2.1 物理备份设备

2.2 逻辑备份设备

逻辑设备是指向特定物理备份设备（磁盘文件或磁带机）的用户定义名称。将备份写入备份设备后，便会初始化物理设备。

3. 本地备份

3.1 三种备份类型

1. 完整备份 (full backup) ： 完整备份
2. 差异备份 (differential backup) :  完整备份后的以来发生更改的数据。
3. 日志备份 (log backup) : 包括以前日志备份中未备份的所有日志记录的事务日志备份。 （完整恢复模式） （定期的进行日志备份，日志备份可以截断事务，可以使得空间重用。）

3.2 基本语法 (Transact-SQL)

3.3 完整备份

3.3.1 如何创建完整备份（Transact-SQL示例）

3.3.2 powershell 完整备份

3.3.3 权限

1. 默认情况下，为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限 。
2. 备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。 SQL Server 服务应对设备进行读写。 运行 SQL Server 服务所用的帐户必须拥有对备份设备的写入权限。 但是，用于在系统表中添加备份设备条目的 sp_addumpdevice 并不检查文件访问权限。 除非使用备份或尝试还原，否则备份设备的物理文件可能不会出现问题。（**注意逻辑设备对应的磁盘权限**）

3.4 差异备份

3.4.1 如何创建差异备份（Transact-SQL示例）

3.4.2 powershell 差异备份

3.4.3 权限

1. BACKUP DATABASE 和 BACKUP LOG 权限默认为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色。
2. 备份设备的物理文件的所有权和权限问题将会妨碍备份操作。 SQL Server 需能够读取和写入设备；运行 SQL Server 服务的帐户必须具有写入权限。 但是，用于在系统表中为备份设备添加项目的 sp_addumpdevice不 检查文件访问权限。 在你因尝试备份或还原而访问物理资源之前，备份设备物理文件中的权限问题并不明显。（**注意逻辑设备对应的磁盘权限**）

3.5 事务日志备份

3.5.1 如何创建事务日志备份（Transact-SQL示例）

3.5.2 powershell 事务日志备份

3.6 提升备份效率的手段

3.6.1 备份到多个文件提高备份效率

1. 备份到多个磁盘，可以充分利用磁盘IO，减少备份时间。
2. 另外如果单个磁盘空间不足的话，备份到多个文件可以使备份文件变小，解决磁盘空间问题，另外通过网络移动到其他地方也比较方便。
3. 备份到多个文件可以SQL Server使用多个备份线程，提供效率。

3.7 查询备份恢复历史

3.7.1 TSQL 查询备份历史

3.7.2 powershell 查询备份历史

3.7.3 TSQL查询恢复历史

4. 还原

4.1 还原顺序

1. 计划还原顺序
   - 创建数据库的结尾日志备份（如果可以）
   - 确定目标恢复点。（目标恢复点可以是事务日志备份中的任何时间点或标记）
   - 确定要执行的还原类型。
   - 标识您需要的备份，并确保必要的介质集和备份设备可用。
2. 执行还原
   - 还原完整备份
   - 还原基于这些完整备份的差异备份
   - 通过按顺序还原日志备份、完成包含恢复点的备份来前滚数据库。是否必须应用所有日志备份取决于日志备份包含什么样的目标恢复点，如下所示：
       （1） 如果恢复点是故障点，则必须还原自上一次还原数据（完整或差异）备份以来创建的所有日志备份。
       （2）对于时点还原，您可能不需要最新的日志备份。只需要恢复到包含时间点的那个日志备份文件并指定时间即可。

4.2 还原到故障点（完整恢复模式）

还原到原库或其他库或者新实例都可以。

 4.2.1 典型的还原顺序图解及步骤

 

步骤如下：

1. 备份活动事务日志（称为日志尾部）。 此操作将创建结尾日志备份。 如果活动事务日志不可用，则该日志部分的所有事务都将丢失。(对应上图t10及红框2，因为最后一个日志备份在t9时刻，则**t9至故障点t10阶段的事务日志是没有备份的，因此这里需要备份这段结尾日志，不备份的话这部分日志就丢失了，数据丢失，除非这段时间没有事务及数据变更**)。
2. 还原最新完整数据库备份而不恢复数据库 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。
3. 如果存在差异备份，则还原最新的差异备份而不恢复数据库 (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY)。 （**还原最新差异备份可减少必须还原的日志备份数。即完整备份和差异备份中间的日志备份是不需要还原的，因为后面的差异备份包含了前面的事务日志备份。另外如果有多个差异备份，则按顺序恢复**）
4. 还原备份后创建的第一个事务日志备份开始，使用 NORECOVERY 依次还原日志。另外，**如果备份了结尾日志，也需要还原这个结尾日志备份**。
5. 恢复数据库 (RESTORE DATABASE database_name WITH RECOVERY)。 此步骤也可以与还原上一次日志备份结合使用。

提示：

1. 有点类似mysql xtrabackup 备份还原，最后一个增备apply-log。恢复事务日志代表重做，最后一步代表回滚。回滚后，数据库将进入联机状态，不能再将其他事务日志备份应用到数据库。
2. WITH NORECOVERY ： 让库处于还原状态（还原状态时库database不可以使用，因为这个时候数据是不完整的，不让使用和更新）。
3. WITH RECOVERY ： 让应用进入恢复后状态（此时，库database是可用了）。

4.2.2 基本语法（Transact-SQL示例）

4.2.3 实际操作（Transact-SQL示例）

4.3 还原到时间点

在完整恢复模式下，完整的数据库还原通常可恢复到日志备份中的某个时间点、标记的事务或 LSN。 但是，**在大容量日志恢复模式下，如果日志备份包含大容量更改，则不能进行时点恢复**。

4.3.1 实操实际操作（Transact-SQL示例）

4.4 powershell 还原

4.5 权限

1. 如果不存在要还原的数据库，则用户必须有 CREATE DATABASE 权限才能执行 RESTORE。 如果数据库存在，则 RESTORE 权限默认授予 sysadmin 和 dbcreator 固定服务器角色成员以及数据库的所有者 (dbo)（对于 FROM DATABASE_SNAPSHOT 选项，数据库始终存在）。
2. RESTORE 权限被授予那些成员身份信息始终可由服务器使用的角色。 因为只有在固定数据库可以访问且没有损坏时（在执行 RESTORE 时并不会总是这样）才能检查固定数据库角色成员身份，所以 db_owner 固定数据库角色成员没有 RESTORE 权限。
3. 文件权限

 4.6 还原到另外一个数据库名（复制数据库）

5. 其他备份还原相关

5.1 结尾日志备份

结尾日志备份说的是还原数据库前的最后一个事务日志备份（日志尾部）。对于使用完整恢复模式或大容量日志恢复模式的数据库，通常需要在开始还原数据库前备份日志尾部。 在对日志传送配置进行故障转移之前，还应当备份主数据库的日志尾部。 将结尾日志备份作为恢复数据库之前的最后一个日志备份还原可以防止失败后丢失工作。

需要结尾日志备份的方案（建议在以下场景中执行结尾日志备份）：

1. 如果数据库处于联机状态并且您计划对数据库执行还原操作，则从备份日志结尾开始。 若要避免联机数据库出错，必须使用 ... BACKUP Transact-SQL 语句的 WITH NORECOVERY 选项。
2. 如果数据库处于脱机状态而无法启动，则需要还原数据库，从备份日志结尾开始。 由于此时不会发生任何事务，因此 WITH NORECOVERY 是可选的。
3. 如果数据库损坏，则尝试使用 BACKUP 语句的 WITH CONTINUE_AFTER_ERROR 选项执行结尾日志备份。

5.2 压缩备份、还原

5.3 加密备份、还原

6. 备份恢复答疑及踩坑记录

6.1. 事务日志不断增长、空间满

1. 原因1： 数据库是完整恢复模式，但是并没有定期的进行日志备份，导致事务日志不断膨胀。
解决方法： 定期进行日志备份

2. 原因2： 有事务长时间没有提交
解决方法： 查找出已经运行完成但没有提交的事务，kill掉此事务即可

3. 原因3： 大事务正在运行（事务很大，一直不停的在记录大量的日志，导致日志增大）
解决办法： 尽量优化业务

6.2 完整备份包含事务日志备份吗？

数据库完整备份将备份整个数据库。 还包括部分的事务日志，以便在还原完整数据库备份后可以恢复完整数据库。其实差异备份也是如此。完整或差异备份需要日志来将数据库还原到当完整或差异备份结束时的事务一致性状态。所以，完整备份或差异备份包含部分事务日志备份。准确的说是从完整备份开始到结束这段时间的事务日志备份。

6.3 完整备份会截断事务日志吗？

首先，我们先搞清楚一个概念，截断日志（log truncating）和日志清理（log clearing）其实是同一件事情，它们表示事务日志的一部分被标记为不再需要，可以覆盖重复使用了（有点类似Oracle下的redo log归档后，可以被重新覆盖了）。在完整或大容量事务日志恢复模式下，只有备份日志才会清除日志。我们知道完整备份会包含事务日志备份，但是它确实不会截断日志(清除日志)，跟多详细细节参考“Misconceptions around the log and log backups: how to convince yourself”

6.4 简单恢复模式下能做事务日志备份吗？

不行。简单的恢复模式下仅允许完整备份和差异数据库备份，并且没有进行事务日志备份的机会。 在简单恢复模型中创建检查点时，将从事务日志中删除所有已提交的事务。

6.5 恢复时报错 ： Exclusive access could not be obtained because the database is in use

问题： 当前库还有活动连接在使用中

6.6 恢复时报错 ： This differential backup cannot be restored because the database has not been restored to the correct earlier state.

问题 ： 说明还原的顺序不对，还原差异备份一定要基于最新的完整备份。

解决方法： 还原差异前选择最近一个完整备份。

6.7 恢复完成，目标库显示“正在还原”

原因：事务未回滚，需要手动回滚事务

解决方法： restore database [db_name] with recovery

6.8 恢复备份文件到另一个库时报错， Logical file 'yywtest' is not part of database 'yywtest'. Use RESTORE FILELISTONLY to list the logical file names.

原因： 逻辑文件名和物理备份文件不匹配。

解决方法： 通过 RESTORE FILELISTONLY FROM DISK = N'D:\baktest\SQLTestDB\SQLTestDB-Full-1.trn' 获取逻辑名，然后还原的时候用这个语句查出来的逻辑名替换。