合 SQL Server备份恢复系列
Tags： MSSQLSQL Server备份恢复增量备份差异备份干货知识点讲解
- 为何备份？
- 术语
- 备份类型
- 差异备份与增量备份的区别
- 备份介质术语和定义
- 备份压缩
- 备份操作限制
- 无法备份脱机数据
- 并发限制
- 创建测试数据库
- 进行备份
- 还原备份
- 清理资源
- 简单恢复模式下的数据库备份
- 示例 (Transact-SQL)
- 完整恢复模式下的数据库备份
- 示例 (Transact-SQL)
- 使用完整数据库备份还原数据库
- 优势
- 差异备份概述
- 具有内存优化表的数据库的差异备份
- 对只读数据库进行差异备份
- 对只读数据库进行差异备份的最佳方法
- 创建差异数据库备份 (SQL Server)
- 准备工作
- 限制和局限
- 先决条件
- 建议
- Security
- 首先检查你的权限！
- SQL Server Management Studio创建差异备份
- Transact-SQL创建差异备份
- 创建差异数据库备份
- 示例 (Transact-SQL)
- 还原差异数据库备份 (SQL Server)
- 开始之前
- 限制和局限
- 先决条件
- Security
- 权限
- 使用 SQL Server Management Studio还原差异数据库备份
- 使用 Transact-SQL还原差异数据库备份
- 示例 (Transact-SQL)
- A. 还原差异数据库备份
- B. 还原数据库、差异数据库以及事务日志备份
- 差异备份和恢复示例
- 在简单恢复模式下还原数据库的概述
- 基本 TRANSACT-SQL RESTORE 语法
- 示例 (Transact-SQL)
- 不受信任的源
- 旧版本中的备份
- 将数据库还原到故障点
- 基本 TRANSACT-SQL RESTORE 语法
- 示例：恢复到故障点 (Transact-SQL)
- 将数据库还原到日志备份中的某个时间点
- 时点还原方案示例
- 开始之前
- 先决条件
- Security
- 权限
- 使用 SQL Server Management Studio
- 还原事务日志备份
- 使用 Transact-SQL
- 还原事务日志备份
- 示例 (Transact-SQL)
- A. 应用单个事务日志备份
- B. 应用多个事务日志备份
- 开始之前
- 建议
- Security
- 权限
- 使用 SQL Server Management Studio
- 使用 Transact-SQL
- 示例 (Transact-SQL)
- 将源库离线或停止SQL Server数据库
- 拷贝数据文件和日志文件
- 在目标服务器上附加数据库
- SQL Server2008R2
- SQL Server 2000
- 备份文件到共享目录
- 还原到新库、新位置、重命名
备份恢复概述
为何备份？
备份 SQL Server 数据库、在备份上运行测试还原过程以及在另一个安全位置存储备份副本可防止可能的灾难性数据丢失。 备份是保护数据的唯一方法 。
使用有效的数据库备份，可从多种故障中恢复数据，例如：
- 介质故障。
- 用户错误（例如，误删除了某个表）。
- 硬件故障（例如，磁盘驱动器损坏或服务器报废）。
- 自然灾难。 通过使用 SQL Server 备份到 Azure Blob 存储服务，可以在本地位置之外的其他区域创建一个站外备份，这样在发生影响本地位置的自然灾难时仍可以使用数据库。
此外，数据库备份对于进行日常管理（如将数据库从一台服务器复制到另一台服务器、设置 Always On 可用性组 或数据库镜像以及进行存档）非常有用。
术语
备份 [动词] (back up)
从 SQL Server 数据库或其事务日志中将数据或日志记录复制到备份设备（如磁盘），以创建数据备份或日志备份。
备份 [名词] (backup)
可用于在失败后还原或恢复数据的 SQL Server 数据副本。 在数据库级别以及针对数据库的一个或多个文件或文件组创建 SQL Server 数据的备份。 不能创建表级备份。 除了数据备份之外，完整恢复模式要求创建事务日志的备份。
恢复模式
用于控制数据库上的事务日志维护的数据库属性。 有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。 数据库的恢复模式确定其备份和还原要求。
还原 (restore)
一种包括多个阶段的过程，用于将指定 SQL Server 备份中的所有数据和日志页复制到指定数据库，然后通过应用记录的更改使该数据在时间上向前移动，以前滚备份中记录的所有事务。
备份类型
常用的数据备份方式有完全备份、差异备份以及增量备份。
完全备份(Full Backup)：备份全部选中的文件夹，并不依赖文件的存档属性来确定备份哪些文件。在备份过程中，任何现有的标记都被清除，每个文件都被标记为已备份。换言之，清除存档属性。完全备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。实际应用中就是用一盘磁带对整个系统进行完全备份，包括其中的系统和所有数据。这种备份方式最大的好处就是只要用一盘磁带，就可以恢复丢失的数据。因此大大加快了系统或数据的恢复时间。
差异备份(Differential Backup)：备份自上一次完全备份之后有变化的数据。差异备份过程中，只备份有标记的那些选中的文件和文件夹。它不清除标记，也即备份后不标记为已备份文件。换言之，不清除存档属性。差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全备份和最后一次差异备份进行恢复。差异备份在避免了另外两种备份策略缺陷的同时，又具备了它们各自的优点。首先，它具有了增量备份需要时间短、节省磁盘空间的优势;其次，它又具有了全备份恢复所需磁带少、恢复时间短的特点。系统管理员只需要两盘磁带，即全备份磁带与灾难发生前一天的差异备份磁带，就可以将系统恢复。
增量备份 (Incremental Backup )：备份自上一次备份(包含完全备份、差异备份、增量备份)之后有变化的数据。增量备份过程中，只备份有标记的选中的文件和文件夹，它清除标记，既：备份后标记文件，换言之，清除存档属性。增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备份后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。
仅复制备份 (copy-only backup)
独立于正常 SQL Server 备份序列的特殊用途备份。
数据备份 (data backup)
完整数据库的数据备份（数据库备份）、部分数据库的数据备份（部分备份）或一组数据文件或文件组的数据备份（文件备份）。
数据库备份 (database backup)
数据库的备份。 完整数据库备份表示备份完成时的整个数据库。 差异数据库备份只包含自最近完整备份以来对数据库所做的更改。
差异备份 (differential backup)
基于完整数据库或部分数据库以及一组数据文件或文件组的最新完整备份的数据备份（差异基准），仅包含自差异基准以来发生了更改的数据区。
部分差异备份仅记录自上一次部分备份（称为“差异基准”）以来文件组中发生更改的数据区。
完整备份 (full backup)
一种数据备份，包含特定数据库或者一组特定的文件组或文件中的所有数据，以及可以恢复这些数据的足够的日志。
日志备份 (log backup)
包括以前日志备份中未备份的所有日志记录的事务日志备份。 （完整恢复模式）
文件备份 (file backup)
一个或多个数据库文件或文件组的备份。
部分备份 (partial backup)
仅包含数据库中部分文件组的数据（包含主要文件组、每个读/写文件组以及任何可选指定的只读文件中的数据）。
差异备份与增量备份的区别
通过上面的概念分析可以知道，差异备份与增量备份的区别在于它们备份的参考点不同：增量备份的参考点是上一次完全备份、差异备份或增量备份，差异备份的参考点是上一次完全备份。
下图展示了差异备份与增量备份的区别，其中Differential为差异备份，Incremental为增量备份：
备份介质术语和定义
备份设备 (backup device)
要将 SQL Server 备份写入其中以及可从其中还原的磁盘或磁带设备。 SQL Server 备份也可以写入 Azure Blob 存储服务，并且使用 URL 格式来指定备份文件的目标和名称。 有关详细信息，请参阅使用 Microsoft Azure Blob 存储服务执行 SQL Server 备份和还原。
备份介质
已写入一个或多个备份的一个或多个磁带或磁盘文件。
备份集 (backup set)
通过成功的备份操作添加到介质组的备份内容。
介质簇 (media family)
在介质集中的单个非镜像设备或一组镜像设备上创建的备份。
介质集 (media set)
备份介质（磁带或磁盘文件）的有序集合，使用固定类型和数量的备份设备向其写入了一个或多个备份操作。
镜像介质集 (mirrored media set)
介质集的多个副本（镜像）。
备份压缩
SQL Server 2008 Enterprise 及更高版本支持压缩备份，并且 SQL Server 2008 及更高版本可以还原压缩后的备份。 有关详细信息，请参阅备份压缩 (SQL Server)。
备份操作限制
可以在数据库在线并且正在使用时进行备份。 但是，存在下列限制：
无法备份脱机数据
隐式或显式引用脱机数据的任何备份操作都会失败。 一些典型示例包括：
您请求完整数据库备份，但是数据库的一个文件组脱机。 由于所有文件组都隐式包含在完整数据库备份中，因此，此操作将会失败。
若要备份此数据库，可以使用文件备份并仅指定联机的文件组。
请求部分备份，但是有一个读/写文件组处于脱机状态。 由于部分备份需要使用所有读/写文件组，因此该操作失败。
请求特定文件的文件备份，但是其中有一个文件处于脱机状态。 该操作失败。 若要备份联机文件，可以省略文件列表中的脱机文件并重复该操作。
通常，即使一个或多个数据文件不可用，日志备份也会成功。 但如果某个文件包含大容量日志恢复模式下所做的大容量日志更改，则所有文件都必须都处于联机状态才能成功备份。
并发限制
SQL Server 可以使用联机备份过程来备份数据库。 在备份过程中，可以进行多个操作；例如：在执行备份操作期间允许使用 INSERT、UPDATE 或 DELETE 语句。 但是，如果在正在创建或删除数据库文件时尝试启动备份操作，则备份操作将等待，直到创建或删除操作完成或者备份超时。
在数据库备份或事务日志备份的过程中无法执行的操作包括：
- 文件管理操作，如含有 ADD FILE 或 REMOVE FILE 选项的 ALTER DATABASE 语句。
- 收缩数据库或文件操作。 这包括自动收缩操作。
- 如果在进行备份操作时尝试创建或删除数据库文件，则创建或删除操作将失败。
如果备份操作与文件管理操作或收缩操作重叠，则产生冲突。 无论哪个冲突操作首先开始，第二个操作总会等待第一个操作设置的锁超时。（超时期限由会话超时设置控制。）如果在超时期限内释放锁，第二个操作将继续执行。 如果锁超时，则第二个操作失败。
参考
快速入门：本地备份和还原 SQL Server 数据库
创建测试数据库
- 启动 SQL Server Management Studio (SSMS) 并连接到 SQL Server 实例。
- 打开“新建查询”窗口。
- 运行以下 TRANSACT-SQL (T-SQL) 代码来创建测试数据库。 刷新对象资源管理器中的“数据库”节点，查看新数据库 。
SQL复制
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | USE [master] GO CREATE DATABASE [SQLTestDB] GO USE [SQLTestDB] GO CREATE TABLE SQLTest ( ID INT NOT NULL PRIMARY KEY, c1 VARCHAR(100) NOT NULL, dt1 DATETIME NOT NULL DEFAULT getdate() ) GO USE [SQLTestDB] GO INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1') INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2') INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3') INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4') INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5') GO SELECT * FROM SQLTest GO |
进行备份
要备份数据库，请执行以下操作：
- 启动 SQL Server Management Studio (SSMS) 并连接到 SQL Server 实例。
- 在对象资源管理器中，展开“数据库”节点 。
- 右键单击数据库，将鼠标悬停在“任务”上，然后选择“备份...” 。
- 在“目标”下，确认备份路径正确。 如需更改它，请选择“删除”以删除现有路径，然后选择“添加”来键入新路径 。 可通过省略号导航到特定文件。
- 选择“确定”以备份数据库。
或者，可运行以下 Transact-SQL 命令来备份数据库：
SQL复制
1 2 3 4 5 | BACKUP DATABASE [SQLTestDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' WITH NOFORMAT, NOINIT, NAME = N'SQLTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
还原备份
要还原数据库，请执行以下操作：
启动 SQL Server Management Studio (SSMS) 并连接到 SQL Server 实例。
在对象资源管理器中右键单击“数据库”节点，然后选择“还原数据库...” 。
选择“设备:”，然后选择省略号 (...) 来查找备份文件。
选择“添加”，然后导航到
.bak文件所在的位置。 选择.bak文件，然后选择“确定”。选择“确定”，关闭“选择备份设备”对话框 。
选择“确定”以还原数据库备份。
或者，可运行以下 Transact-SQL 脚本来还原数据库：
SQL复制
1 2 3 4 | USE [master] RESTORE DATABASE [SQLTestDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 GO |
清理资源
运行以下 Transact-SQL 命令来删除所创建的数据库及其在 MSDB 数据库中的备份历史记录：
SQL复制
1 2 3 4 5 6 | EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLTestDB' GO USE [master] DROP DATABASE [SQLTestDB] GO |
完整数据库备份 (SQL Server)
适用于： SQL Server（所有支持的版本)
完整数据库备份可对整个数据库进行备份。 这包括对部分事务日志进行备份，以便在还原完整数据库备份之后，能够恢复完整数据库备份。 完整数据库备份表示备份完成时的数据库。
提示
随着数据库不断增大，完整备份需花费更多时间才能完成，并且需要更多的存储空间。 因此，对于大型数据库而言，您可以用一系列“差异数据库备份” 来补充完整数据库备份。 有关详细信息，请参阅 差异备份 (SQL Server)。
重要
针对数据库备份，TRUSTWORTHY 设置为 OFF。 有关如何将 TRUSTWORTHY 设置为 ON 的详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。
简单恢复模式下的数据库备份
在简单恢复模式下，每次备份后，如果出现严重故障，数据库将有可能丢失工作。 每次更新都会增加丢失工作的风险，这种情况将一直持续到下一次备份。这时，工作丢失风险将变为零，并开始新一轮的工作丢失风险。 备份之间的工作丢失风险随着时间的推移而增加。 下图显示了仅使用完整数据库备份的备份策略的工作丢失风险。
示例 (Transact-SQL)
下面的示例说明了如何使用 WITH FORMAT 覆盖任意现有备份并创建新介质集，从而创建一个完整数据库备份。
1 2 3 4 5 | -- Back up the AdventureWorks2012 database to new media set. BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorksSimpleRM.bak' WITH FORMAT; GO |
完整恢复模式下的数据库备份
对于使用完整恢复模式和大容量日志恢复模式的数据库而言，数据库备份是必需的，但并不足够。 还需要事务日志备份。 下图显示了在完整恢复模式下可以使用的复杂性最小的备份策略。
有关如何创建日志备份的信息，请参阅事务日志备份 (SQL Server)。
示例 (Transact-SQL)
下面的示例说明了如何使用 WITH FORMAT 覆盖任意现有备份并创建新介质集，从而创建一个完整数据库备份。 然后，此示例将备份事务日志。 在现实情况下，您必须执行一系列的定期日志备份。 在此示例中， AdventureWorks2012 示例数据库设置为使用完整恢复模式。
1 2 3 4 5 6 7 8 9 10 11 | USE master; ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO -- Back up the AdventureWorks2012 database to new media set (backup set 1). BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak' WITH FORMAT; GO --Create a routine log backup (backup set 2). BACKUP LOG AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak'; GO |
使用完整数据库备份还原数据库
您可以通过将数据库从完整数据库备份还原到任意位置的方法一步完成整个数据库的重新创建。 备份中包含了足够的事务日志，这使您能够将数据库恢复到备份完成的时间。 还原的数据库将与还原数据库备份完成时的原始数据库状态相符，但不包含任何未提交的事务。 在完整恢复模式下，随后应还原所有后续日志备份。 恢复数据库后，将回滚未提交的事务。
差异备份 (SQL Server)
适用于： SQL Server（所有支持的版本）
此备份和还原主题与所有 SQL Server 数据库相关。
差异备份基于最新的、以前的完整数据备份。 差异备份仅捕获自该次完整备份后发生更改的数据。 差异备份所基于的完整备份称为差异的“基准” 。 完整备份（仅复制备份除外）可以用作一系列差异备份的基准，包括数据库备份、部分备份和文件备份。 文件差异备份的基准备份可以包含在完整备份、文件备份或部分备份中。
优势
- 与创建完整备份相比，创建差异备份的速度可能非常快。 差异备份只记录自差异备份所基于的完整备份后更改的数据。 这有助于频繁地进行数据备份，减少数据丢失的风险。 但是，在还原差异备份之前，必须先还原其基准。 因此，从差异备份进行还原必然要比从完整备份进行还原需要更多的步骤和时间，因为这需要两个备份文件。
- 如果数据库的某个子集比该数据库的其余部分修改得更为频繁，则差异数据库备份特别有用。 在这些情况下，使用差异数据库备份，您可以频繁执行备份，并且不会产生完整数据库备份的开销。
- 在完整恢复模式下，使用差异备份可以减少必须还原的日志备份的数量。
差异备份概述
差异备份捕获在创建差异基准和创建差异备份之间发生更改的任何 盘区 （物理上连续的八个页的集合）的状态。 这意味着，给定差异备份的大小取决于自建立差异基准后更改的数据量。 通常，差异基准越旧，新的差异备份就越大。 在一系列差异备份中，频繁更新的区可能在每个差异备份中包含不同的数据。
下图显示的是差异备份的工作原理。 该图显示了二十四个数据区，其中的六个已发生更改。 差异备份只包含这六个数据区。 差异备份操作取决于位图页，此页针对每个区包含一位。 对于自建立差异基准后更新的每个区，该位在位图中设置为 1。
备注
仅复制备份不能更新差异位图。 因此，仅复制备份不会影响后续差异备份。
如果提示必须序列化，计划任务、手动、脚本备份都不成功怎么办？