sqlserver：数据库备份和恢复（含时间线）

环境：

• window10 x64 专业版
• sqlsever2014 x64

参考：
sqlserver备份专题(英文)
MSSQL · 最佳实践 · SQL Server三种常见备份
浅谈SQL Server中的事务日志(一)----事务日志的物理和逻辑构架
sql server 数据库备份，完整备份，差异备份，自动备份说明
为什么数据库日志文件这么大
SQL Server中灾难时备份结尾日志(Tail of log)的两种方法

一、sqlserver存储文件类型

sqlserver中有两类文件：数据文件和事务日志文件。
默认情况下，我们新建数据库test后，会有test.mdf和test.ldf两个文件，它们分别存储数据和事务日志。

1.1 数据文件：

这里存储的就是现有的数据。比如：我们插入数据，然后将这个数据删除，那么数据文件里是没有这个数据的。

1.2 事务日志文件：

这里存储的是我们所有的历史操作。比如：我们插入数据，然后将这个数据删除，那么事务日志文件中存储了插入和删除的动作。

1.3 数据完整性

一个完整的数据库，必然是它的数据和事务日志在某一个时间点能对的上，并且事务日志能追溯到建库阶段。
我们恢复一个数据库，也就是要达到这个效果。

二、数据库的备份

2.1 三种备份方式

数据库备份的方式有如下三种，它们的概念如下：

• 完整备份
  这种备份将数据文件和事务日志文件进行一起压缩，然后存放到了备份介质中（如：文件test.bak）。
  当我们恢复的时候，可以根据这次的备份结果进行完整的恢复。

• 事务日志备份
  这种日志备份是将上次事务日志备份或完整备份后，数据库又修改的操作备份到备份介质中（注意：仅备份事务日志，不备份数据）。
  我们可以连续的进行事务日志备份，这样就形成了备份链，它们之间是后者依赖前者。所以当我们根据事务日志进行恢复的时候，必须提供完整的事务日志备份链并且这个备份链的开头必须是一个完整备份。

  注意： 为了能进行事务日志备份，我们必须先进行完整备份。可以从下图中的提示中看的出来：
  

• 差异备份
  这种备份记录的是从上次完整备份到当前的差异部分（注意：包含数据差异和日志差异，下面会有实验证明）。它和事务日志的备份除了在备份内容的不同之外，它们的参照点也不同。事务日志备份参照的是上次事务备份或上次完整备份，而差异备份仅参照上次完整备份。

  注意： 为了能进行差异备份，我们必须先进行完整备份。可以从下图中的提示中看的出来：
  

2.2 关于备份和还原的操作记录

在上面讲事务日志备份和差异备份的时候，我们知道必须先进行完整备份，那么系统是怎么判断的呢？
其实，数据库服务器会将我们对服务器上的数据库进行的所有备份和还原操作都记录到系统数据库msdb中。
服务器凭借这里面的记录，决定我们能不能进行事务日志备份和差异备份，并且决定我们备份的参照点。
所以说：对于完整备份后的事务日志备份结果要可靠的存储起来，不能丢失，知道你进行了下一次完整备份。
下面的sql语句是用来查询备份和还原操作历史的：

查看备份操作记录：

SELECT 
     bs.backup_set_id,
     bs.database_name,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
GO

查看还原操作记录：

SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
GO

示例如下：

2.3 关于备份介质和备份集

• 备份介质

  其实就是备份后存储到的位置，比如：test2.bak文件。

• 备份集

  表示的是一次备份。结合备份介质去理解就是：我们可以将多次备份都指定到相同的备份介质中（比如：test2.bak），这样test2.bak文件中就有多次备份的数据。那么，对于test2.bak中每一次的备份，我们都称之为“备份集”。

2.4 关于还原

从操作的对象上来划分，有以下两类：

• 在现有的数据库上进行恢复

  比如说，服务器上已经有test2数据库了，我们想根据另外一个test2.bak备份文件对这个test2数据库进行恢复。因为这个还牵扯到结尾日志的概念，所以先不深入。

• 在服务器上以新建的方式进行恢复（下面的实验，如无说明，均使用这种方式）

  这个和上面的相反，服务器上原来没有test2数据库，我们根据test2.bak文件可以直接以恢复的方式建一个test2数据库。

三、 实验一：证明差异备份存储的是数据差异和事务日志差异

新建数据库test2，日志增量改为1M，其他默认：

立刻做完整备份：

数据库中查看备份记录：

什么都不做，连续做两个差异备份（第一个备份集：test2-差异1，第二个备份集：test2-差异2），观察文件大小变化：


可以看到，尽管两次备份时并没有差异，备份文件依然增长，第一次增长了397k大小，第二次增长了469k，其实这很好理解：记录这次备份的信息也需要存储空间吧。
现在，我们用程序，重复的插入删除数据：


操作完后，我们观察下，数据文件和日志文件的大小：

到这里，我们应该很清楚的知道：事务日志记录的是历史操作。
这个时候，我们进行差异备份，备份集命名为 test2-差异3：

从这里可以看到备份文件仅增长22k了，这个时候，你可能认为“差异备份就是只备份了数据的差异吗”，但是，接着往下看。
此时将test2脱机：

然后我们将test.mdf和test2.ldf改个名字：

然后，将test2数据库删除后，我们还能观察到改名后的文件。
此时，我们使用恢复功能直接恢复到最后一次差异的状态（也可以拷贝到另一台机器上进行恢复，这样会更具有说服力）：

然后，观察数据文件：

这个时候，我们奇迹的发现：恢复的日志文件竟然和之前的一样多，到这里，我们就证明了，差异备份也是备份事务日志差异的。
那么，为什么差异备份后，事务日志的40多M数据没有到备份文件中去呢？
其实进去了，只不过压缩了，因为我们用的sql语句太过有规律了。

四、关于时间线（完整备份和差异备份）

我们考虑这样一个操作过程：

• 新建数据库test
• 完整备份1test（2020-07-25 00:07:06.000）
• 差异备份1（2020-07-25 00:07:33.000 )
• 差异备份2（2020-07-25 00:07:57.000)
• 完整备份2test（2020-07-25 00:08:30.000)
• 完整备份3test（2020-07-25 00:08:55.000）
• 差异备份3（2020-07-25 00:09:18.000）

上面操作后的文件为：

那么当我们恢复时，界面上默认：

也就是说，界面上认为我们可能需要恢复到最近一次完整备份以后的（差异备份3依赖完整备份3，所以选中差异备份3必须选中完整备份3）。
但是，我们可以通过时间线，恢复到之前我们备份的任意一个时间点：

五、事务日志的时间线

我们考虑这样一个操作过程：

• 新建数据库test
• test-完整1（2020-07-25 00:26:33.000）
• test-事务日志1（2020-07-25 00:26:55.000 )
• test-事务日志2（2020-07-25 00:27:09.000)
• test-完整2（2020-07-25 00:27:25.000)
• test-事务日志3（2020-07-25 00:27:42.000）

上面操作后的文件为：

那么当我们恢复时，界面上默认：

可以看到，系统认为我们希望还原的是最近一次完整备份以后的。
其实，通过时间线，我们完全可以恢复到指定的时间：

观察，这个事务日志的恢复可以指定任意时间段内的任意一个点（因为事务日志中记录了这种连续的操作），不像差异备份中的时间点是离散的。

六、在正在运行的数据库上进行恢复

上面我们做实验的时候，故意采用新建数据库的方式进行恢复，那么如果在正在运行的数据库上进行恢复呢？
这种情况理解起来比较绕，因为牵扯到“结尾日志”的概念。

6.1 结尾日志

结尾日志是从还未备份的日志，即：从上次事务日志备份或完整备份到现在这段时间的事务日志。

6.2 什么时候会遇到结尾日志备份

一般我们遇到结尾日志备份的情况就是：我们在正在运行的数据库上进行数据库的还原。
试想一下：
假如我们在1：00进行了完整备份，当2：00的时候，我们用这个完整备份对数据库进行还原的时候是不是就代表1：00 至 2：00这段时间的数据丢失了？
所以说：当我们对现有数据库进行还原的时候，系统就要求我们必须进行结尾日志备份！如下图所示：

如果我们把结尾日志备份去掉：

6.3 是否应该在正在运行的数据库上进行恢复

一般，我们不会对正在运行的数据库进行恢复。。。
为什么这么说呢？因为，我们备份数据库肯定是为了防止数据丢失的嘛，那么，既然现在数据库运行正常，我们为什么要进行恢复呢？
如果你对这块有兴趣，可以百度sqlserver lsn

七、数据库自动备份建议

可以使用数据库的维护计划定时去备份数据库
配置方法参照：https://blog.csdn.net/u010476739/article/details/107620753

建议备份计划：

• 每周星期日的2:00:00执行数据库的完整备份；
• 每周星期一至星期六每天的2:00:00执行数据库的差异备份；
• 每天在8:00:00和23:59:59之间、每1小时执行数据库的日志备份；
• 每个月的最后一个星期日的1:00:00执行数据库的完整备份；