2023年7月30日发(作者：)

sqlserver：数据库备份和恢复（含时间线）环境：window10 x64 专业版sqlsever2014 x64参考：⼀、sqlserver存储⽂件类型sqlserver中有两类⽂件：数据⽂件和事务⽇志⽂件。默认情况下，我们新建数据库test后，会有和两个⽂件，它们分别存储数据和事务⽇志。1.1 数据⽂件：这⾥存储的就是现有的数据。⽐如：我们插⼊数据，然后将这个数据删除，那么数据⽂件⾥是没有这个数据的。1.2 事务⽇志⽂件：这⾥存储的是我们所有的历史操作。⽐如：我们插⼊数据，然后将这个数据删除，那么事务⽇志⽂件中存储了插⼊和删除的动作。1.3 数据完整性⼀个完整的数据库，必然是它的数据和事务⽇志在某⼀个时间点能对的上，并且事务⽇志能追溯到建库阶段。我们恢复⼀个数据库，也就是要达到这个效果。⼆、数据库的备份2.1 三种备份⽅式数据库备份的⽅式有如下三种，它们的概念如下：完整备份这种备份将数据⽂件和事务⽇志⽂件进⾏⼀起压缩，然后存放到了备份介质中（如：⽂件）。当我们恢复的时候，可以根据这次的备份结果进⾏完整的恢复。事务⽇志备份这种⽇志备份是将上次事务⽇志备份或完整备份后，数据库⼜修改的操作备份到备份介质中（注意：仅备份事务⽇志，不备份数据）。我们可以连续的进⾏事务⽇志备份，这样就形成了备份链，它们之间是后者依赖前者。所以当我们根据事务⽇志进⾏恢复的时候，必须提供完整的事务⽇志备份链并且这个备份链的开头必须是⼀个完整备份。注意： 为了能进⾏事务⽇志备份，我们必须先进⾏完整备份。可以从下图中的提⽰中看的出来：差异备份这种备份记录的是从上次完整备份到当前的差异部分（注意：包含数据差异和⽇志差异，下⾯会有实验证明）。它和事务⽇志的备份除了在备份内容的不同之外，它们的参照点也不同。事务⽇志备份参照的是上次事务备份或上次完整备份，⽽差异备份仅参照上次完整备份。注意： 为了能进⾏差异备份，我们必须先进⾏完整备份。可以从下图中的提⽰中看的出来：2.2 关于备份和还原的操作记录在上⾯讲事务⽇志备份和差异备份的时候，我们知道必须先进⾏完整备份，那么系统是怎么判断的呢？其实，数据库服务器会将我们对服务器上的数据库进⾏的所有备份和还原操作都记录到系统数据库msdb中。服务器凭借这⾥⾯的记录，决定我们能不能进⾏事务⽇志备份和差异备份，并且决定我们备份的参照点。所以说：对于完整备份后的事务⽇志备份结果要可靠的存储起来，不能丢失，知道你进⾏了下⼀次完整备份。下⾯的sql语句是⽤来查询备份和还原操作历史的：查看备份操作记录：SELECT

_set_id, se_name, _start_date, _finish_date, CAST(CAST(_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size], CAST(DATEDIFF(second, _start_date, _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, al_device_name, CAST(_lsn AS VARCHAR(50)) AS first_lsn, CAST(_lsn AS VARCHAR(50)) AS last_lsn, _name, ry_model FROM set bs INNER JOIN mediafamily bmf

ON _set_id = _set_id ORDER BY _name,se_name,_start_date;GO查看还原操作记录：SELECT

rs.[restore_history_id], rs.[restore_date], rs.[destination_database_name], al_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] rsINNER JOIN [msdb].[dbo].[backupset] bs--on _set_id = _set_idON _set_id = _set_idINNER JOIN mediafamily bmf

ON _set_id = _set_idGO⽰例如下：2.3 关于备份介质和备份集备份介质其实就是备份后存储到的位置，⽐如：⽂件。备份集表⽰的是⼀次备份。结合备份介质去理解就是：我们可以将多次备份都指定到相同的备份介质中（⽐如：），这样⽂件中就有多次备份的数据。那么，对于中每⼀次的备份，我们都称之为“备份集”。2.4 关于还原从操作的对象上来划分，有以下两类：在现有的数据库上进⾏恢复⽐如说，服务器上已经有test2数据库了，我们想根据另外⼀个备份⽂件对这个test2数据库进⾏恢复。因为这个还牵扯到结尾⽇志的概念，所以先不深⼊。在服务器上以新建的⽅式进⾏恢复（下⾯的实验，如⽆说明，均使⽤这种⽅式）这个和上⾯的相反，服务器上原来没有test2数据库，我们根据⽂件可以直接以恢复的⽅式建⼀个test2数据库。三、 实验⼀：证明差异备份存储的是数据差异和事务⽇志差异新建数据库test2，⽇志增量改为1M，其他默认：⽴刻做完整备份：数据库中查看备份记录：什么都不做，连续做两个差异备份（第⼀个备份集：test2-差异1，第⼆个备份集：test2-差异2），观察⽂件⼤⼩变化：可以看到，尽管两次备份时并没有差异，备份⽂件依然增长，第⼀次增长了397k⼤⼩，第⼆次增长了469k，其实这很好理解：记录这次备份的信息也需要存储空间吧。现在，我们⽤程序，重复的插⼊删除数据：操作完后，我们观察下，数据⽂件和⽇志⽂件的⼤⼩：到这⾥，我们应该很清楚的知道：事务⽇志记录的是历史操作。这个时候，我们进⾏差异备份，备份集命名为 test2-差异3：从这⾥可以看到备份⽂件仅增长22k了，这个时候，你可能认为“差异备份就是只备份了数据的差异吗”，但是，接着往下看。此时将test2脱机：然后我们将和改个名字：然后，将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）。但是，我们可以通过时间线，恢复到之前我们备份的任意⼀个时间点：五、事务⽇志的时间线我们考虑这样⼀个操作过程：新建数据库testtest-完整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七、数据库⾃动备份建议建议备份计划：每周星期⽇的2:00:00执⾏数据库的完整备份；每周星期⼀⾄星期六每天的2:00:00执⾏数据库的差异备份；每天在8:00:00和23:59:59之间、每1⼩时执⾏数据库的⽇志备份；每个⽉的最后⼀个星期⽇的1:00:00执⾏数据库的完整备份；