摘要

在SQL Server备份专题分享中，前两期我们分享了三种常见的备份以及备份策略的制定，在第三期分享中，我们将要分享SQL Server的数据库备份链。完整的数据库备份链是保证数据库能够实现灾难恢复的基础，如果备份链条被打断或者备份链条上的文件损坏，势必会导致数据恢复不完整或者不能满足预期，而造成数据丢失，危害数据完整性生命线，后果非常严重。所以，理解SQL Server数据库备份链非常重要。

MSSQL· 最佳实践· SQL Server三种常见备份

MSSQL · 最佳实践 · SQL Server备份策略

场景引入

在开始今天关于SQL Server数据库备份链的分享之前，我们还是以上一期SQL Server备份策略分享的场景引入。假设我们存在如下图所示的备份文件列表，为什么我们就可以使用“全备 + 差备 + 日志备份”来实现快速的数据库还原操作呢？我们又是如何快速的查找到TestDR数据库的备份链条，并以此来还原的呢？这两个话题是我们今天要解答和解决的。 TestDR数据库备份文件列表如下图所示： 01.png

LSN介绍

在解决今天的问题之前，我需要首先介绍一个非常重要的概念：LSN。SQL Server的每一个事务日志都有自己的唯一标识号Log Sequence Number(简写为LSN)。LSN会随着时间的推移和事务日志的产生而不断增大。那么在SQL Server中如何查看以及有几种类型的LSN呢？

备份文件LSN

在SQL Server中，查看备份的LSN非常简单，我们可以使用RESTORE HEADERONLY命令跟上备份文件路径，即可查看。


1. RESTORE HEADERONLY FROM DISK='C:\Temp\TestDR_20171217@00:00_FULL.bak'
2. RESTORE HEADERONLY FROM DISK='C:\Temp\TestDR_20171217@01:00_LOG.trn'

执行的展示结果如下： 02.png

LSN种类

从以上截图红色方框中我们可以看出来，SQL Server的LSN分为以下四类：

  • FirstLSN：备份集中第一个事务日志的LSN；
  • LastLSN：备份集中最后一个事务日志的下一个LSN；
  • CheckpointLSN：最后一个Checkpoint发生时的LSN；
  • DatabaseBackupLSN：最后一个FULL Backup备份的LSN。

备份链原理

LSN是查找SQL Server 数据库备份链不可或缺的神一般存在的关键信息。我们可以利用前面讲到的四类LSN，使用如下五步骤来实现SQL Server备份链的查找。

  • 获取Full Backup: 查找最新一个FULL Backup文件，拿到对应CheckpointLSN；
  • 获取Diff Backup：查找Diff Backup文件，Diff Backup文件的DatabaseBackupLSN等于第一步中的CheckpointLSN，如果有多个Diff Backup文件，取FirstLSN最大者，然后拿到相应LastLSN；
  • 获取第一个Log Backup：查找第二步中的LastLSN位于Log Backup文件的FirstLSN和LastLSN之间的Log Backup，并获取相应的LastLSN；
  • 获取下一个Log Backup: 下一个Log Backup文件的FirstLSN等于第三步中获取到额LastLSN；
  • 获取第N个Log Backup：按照步骤四中的方法查找，直到所有日志文件查找完毕后，一个完整的备份链条查找完毕。

以上的理论解释稍显枯燥，我们用具体的示例来解释，就显得更为丰满和易于理解。我们把“场景引入”中的十五个备份文件，参照“备份文件LSN”中的方法来获取到LSN，如下面的截图所示： 03.png

那么，TestDR数据库的备份链可以通过如下的方法来查找：

  • 获取Full Backup: 查找最新一个FULL Backup（BackType=1）文件，拿到CheckpointLSN：24000000012800197，图中红色方框标示；
  • 获取Diff Backup：查找Diff Backup（BackType=5）文件，Diff Backup文件的DatabaseBackupLSN等于第一步中的CheckpointLSN:24000000012800197，图中黄色方框标示，取FirstLSN最大者: 24000000037600007，然后拿到LastLSN: 24000000039200001；
  • 获取第一个Log Backup：查找第二步中的LastLSN（24000000039200001）位于Log Backup（BackType=5）的FirstLSN（24000000036800001）与LastLSN（24000000040000001）之间的Log Backup，如图中绿色方框标示，并获取到LastLSN：24000000040000001；
  • 获取下一个Log Backup: 下一个Log Backup文件的FirstLSN（24000000040000001）应该等于上一步获取到额LastLSN，如图中粉红色标示；
  • 获取第N个Log Backup：按照步骤四中的方法查找，直到所有日志文件查找完毕。 所以，我们最终获取到的TestDR数据库的最简单，快速恢复的备份链是：

1. C:\Temp\TestDR_20171217@00:00_FULL.bak => C:\Temp\TestDR_20171217@12:00_DIFF.bak => C:\Temp\TestDR_20171217@13:00_LOG.trn => C:\Temp\TestDR_20171217@14:00_LOG.trn。

这个链条和我们上一期分享的数据库备份文件还原操作是完全吻合的。

具体实现

在“备份链原理”章节中，我们找出备份链是通过“用眼睛看”的查找方式来实现的。但是现实中如果数据库实例众多，数据库纷繁复杂，用眼睛看的方法就显得低效而准确率不高。那么，如何实现数据库备份链的自动查找呢？不着急，我已经为大家写好了，你只需要把相同数据库的多个备份文件路径放入输入参数中，我们就非常轻松、快捷的查找到数据库的备份链，并以此为依据来还原数据库。还是以TestDR的十五个备份文件为例来查找备份链条，代码如下：


1. USE master
2. GO

5. SET NOCOUNT ON
6. DECLARE
7. -- all the backup files
8. @backup_file_list NVARCHAR(MAX) = N'
9. C:\Temp\TestDR_20171217@00:00_FULL.bak
10. C:\Temp\TestDR_20171217@01:00_LOG.trn
11. C:\Temp\TestDR_20171217@02:00_LOG.trn
12. C:\Temp\TestDR_20171217@03:00_LOG.trn
13. C:\Temp\TestDR_20171217@04:00_LOG.trn
14. C:\Temp\TestDR_20171217@05:00_LOG.trn
15. C:\Temp\TestDR_20171217@06:00_DIFF.bak
16. C:\Temp\TestDR_20171217@07:00_LOG.trn
17. C:\Temp\TestDR_20171217@08:00_LOG.trn
18. C:\Temp\TestDR_20171217@09:00_LOG.trn
19. C:\Temp\TestDR_20171217@10:00_LOG.trn
20. C:\Temp\TestDR_20171217@11:00_LOG.trn
21. C:\Temp\TestDR_20171217@12:00_DIFF.bak
22. C:\Temp\TestDR_20171217@13:00_LOG.trn
23. C:\Temp\TestDR_20171217@14:00_LOG.trn
24. '
25. ;

27. -- Create temp table to save headeronly info
28. IF OBJECT_ID('tempdb..#headeronly') IS NOT NULL
29. DROP TABLE #headeronly
30. CREATE TABLE #headeronly(
31. RowId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
32. ,BackupName    nvarchar(128) NULL
33. ,BackupDescription    nvarchar(255) NULL
34. ,BackupType    smallint  NULL
35. ,ExpirationDate    datetime
36. ,Compressed    bit  NULL
37. ,Position    smallint  NULL
38. ,DeviceType    tinyint     NULL
39. ,UserName    nvarchar(128)  NULL
40. ,ServerName    nvarchar(128)  NULL
41. ,DatabaseName    nvarchar(128)  NULL
42. ,DatabaseVersion    int      NULL
43. ,DatabaseCreationDate    datetime  NULL
44. ,BackupSize    numeric(20,0)  NULL
45. ,FirstLSN    numeric(25,0)  NULL
46. ,LastLSN    numeric(25,0)  NULL
47. ,CheckpointLSN    numeric(25,0)  NULL
48. ,DatabaseBackupLSN    numeric(25,0)  NULL
49. ,BackupStartDate    datetime  NULL
50. ,BackupFinishDate    datetime  NULL
51. ,SortOrder    smallint  NULL
52. ,CodePage    smallint  NULL
53. ,UnicodeLocaleId    int  NULL
54. ,UnicodeComparisonStyle    int  NULL
55. ,CompatibilityLevel    tinyint  NULL
56. ,SoftwareVendorId    int  NULL
57. ,SoftwareVersionMajor    int  NULL
58. ,SoftwareVersionMinor    int  NULL
59. ,SoftwareVersionBuild    int  NULL
60. ,MachineName    nvarchar(128)  NULL
61. ,Flags    int  NULL
62. ,BindingID    uniqueidentifier  NULL
63. ,RecoveryForkID    uniqueidentifier  NULL
64. ,Collation    nvarchar(128)  NULL
65. ,FamilyGUID    uniqueidentifier  NULL
66. ,HasBulkLoggedData    bit  NULL
67. ,IsSnapshot    bit  NULL
68. ,IsReadOnly    bit  NULL
69. ,IsSingleUser    bit  NULL
70. ,HasBackupChecksums    bit  NULL
71. ,IsDamaged    bit  NULL
72. ,BeginsLogChain    bit  NULL
73. ,HasIncompleteMetaData    bit  NULL
74. ,IsForceOffline    bit  NULL
75. ,IsCopyOnly    bit  NULL
76. ,FirstRecoveryForkID    uniqueidentifier   NULL
77. ,ForkPointLSN    numeric(25,0) NULL
78. ,RecoveryModel    nvarchar(60)  NULL
79. ,DifferentialBaseLSN    numeric(25,0)  NULL
80. ,DifferentialBaseGUID    uniqueidentifier  NULL
81. ,BackupTypeDescription    nvarchar(60)  NULL
82. ,BackupSetGUID    uniqueidentifier NULL
83. ,CompressedBackupSize    bigint  NULL
84. );

86. -- private variables
87. DECLARE
88. @backup_file_list_xml xml
89. ,@backup_file sysname
90. ,@row_count int
91. ,@do int = 0
92. ,@sql NVARCHAR(MAX) = N''
93. ,@full_backup_CheckPointLSN numeric(25,0)
94. ,@full_backup_familyGUID uniqueidentifier
95. ,@diff_backup_LastLSN numeric(25,0)
96. ,@log_backup_LastLSN numeric(25,0)
97. ,@back_chain_level_number INT = 0
98. ,@full_backup_rowid INT
99. ,@diff_backup_rowid INT
100. ;

102. SELECT
103. @backup_file_list_xml = '<V><![CDATA[' + REPLACE(
104. REPLACE(
105. REPLACE(
106. @backup_file_list,CHAR(10),']]></V><V><![CDATA['
107. ),',',']]></V><V><![CDATA['
108. ),CHAR(13),']]></V><V><![CDATA['
109. ) + ']]></V>'
110. ;

112. DECLARE
113. @tb_back_files TABLE(
114. Rowid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
115. backup_file sysname not null
116. );

118. DECLARE
119. @tb_back_file_rowid TABLE(
120. Bak_row_id INT NOT NULL,
121. Level_Number INT NOT NULL
122. );

124. -- split backup files into @table
125. INSERT INTO @tb_back_files
126. SELECT
127. DISTINCT
128. backup_file = RTRIM(LTRIM(T.C.value('(./text())[1]','sysname')))
129. FROM @backup_file_list_xml.nodes('./V') AS T(C)
130. WHERE T.C.value('(./text())[1]','sysname') IS NOT NULL
131. AND LEFT(RTRIM(LTRIM(T.C.value('(./text())[1]','sysname'))), 2) <> '--'
132. ORDER BY 1 ASC

134. -- cursor for each backup file to get headeronly info
135. DECLARE CUR_backup_file CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
136. FOR
137. SELECT
138. backup_file
139. FROM @tb_back_files
140. ORDER BY Rowid ASC

142. OPEN CUR_backup_file
143. FETCH NEXT FROM CUR_backup_file INTO @backup_file

145. SET @row_count = @@CURSOR_ROWS

147. WHILE @@FETCH_STATUS = 0
148. BEGIN
149. RAISERROR('--=== %d/%d ===Processing on %s', 10, 1, @do, @row_count, @backup_file) WITH NOWAIT
150. SET
151. @sql = N'RESTORE HEADERONLY FROM DISK = N' + QUOTENAME(@backup_file, '''') + N';'
152. ;
153. INSERT INTO #headeronly
154. EXEC sys.sp_executesql @sql
155. ;

157. SET @do = @do + 1
158. FETCH NEXT FROM CUR_backup_file INTO @backup_file
159. END

161. CLOSE CUR_backup_file
162. DEALLOCATE CUR_backup_file

164. /**
165. * FULL backup file checking
166. *1. make sure those backup files came from the same database.
167. *2. there is  no full backup
168. *3. There have more than one FULL backup files.
169. */
170. -- make sure backup files came from the same database.
171. IF EXISTS(
172. SELECT TOP 1 1
173. FROM #headeronly
174. GROUP BY FamilyGUID
175. HAVING COUNT(DISTINCT FamilyGUID) >= 2
176. )
177. BEGIN
178. RAISERROR('Those backup fils didn''t backup from the same database, process terminated.', 16, 1) WITH NOWAIT
179. RETURN
180. END

182. -- There is no full backup file
183. IF NOT EXISTS(
184. SELECT TOP 1 1
185. FROM #headeronly
186. WHERE BackupType = 1 -- FULL Backup
187. )
188. BEGIN
189. RAISERROR('There is no FULL BACKUP included, process terminated.', 16, 1) WITH NOWAIT
190. RETURN
191. END

193. -- Have more than one FULL Backup, we don't know use which one.
194. IF EXISTS(
195. SELECT TOP 1 1
196. FROM #headeronly
197. WHERE BackupType = 1
198. GROUP BY BackupType
199. HAVING COUNT(1) >= 2
200. )
201. BEGIN
202. RAISERROR('Those backup fils are more than one FULL BACKUP including, we may don''t know use which one, process terminated.', 16, 1) WITH NOWAIT
203. RETURN
204. END

206. -- We have only one full backup, get FULL Backup file CheckPointLSN
207. SELECT TOP 1
208. @full_backup_CheckPointLSN = CheckPointLSN
209. ,@full_backup_familyGUID = FamilyGUID
210. ,@back_chain_level_number = 0
211. ,@full_backup_rowid = RowId
212. FROM #headeronly
213. WHERE BackupType = 1

215. INSERT INTO @tb_back_file_rowid
216. SELECT @full_backup_rowid, @back_chain_level_number;

218. PRINT 'We got full backup check point LSN: ' + cast(@full_backup_CheckPointLSN as varchar)

221. /**
222. *get the very last diff backup file
223. **/
224. -- if exists diff backup, get the newest one whose DatabaseBackupLSN equals @full_backup_CheckPointLSN got above
225. IF EXISTS(
226. SELECT TOP 1 1
227. FROM #headeronly
228. WHERE BackupType = 5 -- DIFF Backup
229. AND DatabaseBackupLSN = @full_backup_CheckPointLSN
230. AND FamilyGUID = @full_backup_familyGUID
231. )
232. BEGIN
233. RAISERROR('We are searching the Differential Database Backup.', 10, 1) WITH NOWAIT

235. SELECT TOP 1
236. @diff_backup_LastLSN = LastLSN
237. ,@back_chain_level_number = @back_chain_level_number + 1
238. ,@diff_backup_rowid = RowId
239. FROM #headeronly
240. WHERE BackupType = 5 -- DIFF Backup
241. AND DatabaseBackupLSN = @full_backup_CheckPointLSN
242. AND FamilyGUID = @full_backup_familyGUID
243. ORDER BY FirstLSN DESC -- get the max firstLSN (means newest one)

245. INSERT INTO @tb_back_file_rowid
246. SELECT @diff_backup_rowid, @back_chain_level_number;

248. PRINT 'We got newest Differential backup Last LSN: ' + cast(@diff_backup_LastLSN as varchar)
249. END
250. ELSE
251. RAISERROR('We didn''t find any differential backups yet match the full backup.', 10, 1) WITH NOWAIT

253. -- get the very first log backup whose FirstLSN littler and LastLSN greater than @diff_backup_LastLSN
254. -- when exists differential backup
255. IF @diff_backup_LastLSN IS NOT NULL
256. BEGIN
257. IF EXISTS(
258. SELECT TOP 1 1
259. FROM #headeronly
260. WHERE BackupType = 2 -- Log backup
261. AND FirstLSN <@diff_backup_LastLSN
262. AND LastLSN>= @diff_backup_LastLSN
263. AND FamilyGUID = @full_backup_familyGUID
264. )
265. BEGIN
266. SELECT TOP 1
267. @log_backup_LastLSN = LastLSN
268. ,@back_chain_level_number = @back_chain_level_number + 1
269. FROM #headeronly
270. WHERE BackupType = 2
271. AND FirstLSN <@diff_backup_LastLSN
272. AND LastLSN>= @diff_backup_LastLSN
273. AND FamilyGUID = @full_backup_familyGUID

275. PRINT 'We got log backup Last LSN: ' + cast(@log_backup_LastLSN as varchar)
276. END
277. END
278. ELSE
279. -- There is no differential backup, we have to find between log backups.
280. -- Find the very first log backup LastLSN
281. BEGIN
282. SELECT TOP 1
283. @log_backup_LastLSN = LastLSN
284. ,@back_chain_level_number = @back_chain_level_number + 1
285. FROM #headeronly
286. WHERE BackupType = 2 -- Log Backup
287. AND FamilyGUID = @full_backup_familyGUID
288. AND DatabaseBackupLSN = @full_backup_CheckPointLSN
289. AND FirstLSN = @full_backup_CheckPointLSN
290. END

292. -- There is no log backup belongs to this full backup
293. IF @log_backup_LastLSN IS NULL
294. BEGIN
295. RAISERROR('There is no any log backups belong to this full backup.', 10, 1) WITH NOWAIT
296. RETURN
297. END

299. --SELECT
300. --    full_backup_CheckPointLSN = @full_backup_CheckPointLSN
301. --    ,diff_backup_LastLSN = @diff_backup_LastLSN
302. --    ,log_backup_LastLSN = @log_backup_LastLSN
303. --    ,back_chain_level_number = @back_chain_level_number
304. --;

306. -- find out all the log backup need to be restore
307. ;
308. WITH backup_files
309. AS
310. (
311. SELECT lv = @back_chain_level_number,*
312. FROM #headeronly
313. WHERE LastLSN = @log_backup_LastLSN
314. AND FamilyGUID = @full_backup_familyGUID
315. UNION ALL
316. SELECT bf.lv + 1,lg.*
317. FROM #headeronly AS lg
318. INNER JOIN backup_files as bf
319. ON lg.FirstLSN = bf.LastLSN AND lg.FamilyGUID = bf.FamilyGUID
320. WHERE lg.BackupType = 2
321. AND lg.FamilyGUID = @full_backup_familyGUID
322. )

324. INSERT INTO @tb_back_file_rowid
325. SELECT RowId, lv FROM backup_files

327. SELECT
328. bkf.Backup_file,
329. hd.BackupType,
330. BackupTypeDescription =
331. CASE hd.BackupType
332. WHEN 1 THEN 'FULL'
333. WHEN 2 THEN 'LOG'
334. WHEN 5 THEN 'DIFF'
335. ELSE ''
336. END,
337. hd.IsDamaged,
338. restore_order = bkr.Level_Number
339. FROM @tb_back_file_rowid AS bkr
340. INNER JOIN @tb_back_files AS bkf
341. ON bkr.Bak_row_id = bkf.Rowid
342. INNER  JOIN #headeronly AS hd
343. ON bkr.Bak_row_id = hd.RowId
344. ORDER BY bkr.Level_Number

346. --SELECT *
347. --FROM #headeronly
348. --ORDER BY rowid asc

350. SELECT
351. bkf.backup_file,
352. hd.BackupType,
353. hd.Compressed,
354. hd.DatabaseName,
355. hd.FirstLSN,
356. hd.LastLSN,
357. hd.CheckpointLSN,
358. hd.DatabaseBackupLSN,
359. hd.IsDamaged,
360. hd.BackupTypeDescription
361. FROM #headeronly AS hd
362. INNER JOIN @tb_back_files AS bkf
363. ON hd.RowId = bkf.Rowid
364. ORDER BY hd.RowId

备份链结果截图展示如下： 04.png

从这个截图中，restore_order和Backup_file字段可以很清楚的看到数据库还原的顺序，以及相应的备份文件路径。所有备份文件的LSN分布如下截图： 05.png

差异备份文件损坏

从备份链条我们发现，12:00的这个差异备份C:\Temp\TestDR_20171217@12:00_DIFF.bak非常关键，假设现实中，我们发现这个文件恰恰损坏掉了，那么我们的可以实现应用的数据库还原吗？答案是肯定的，我们把刚才的脚本输入参数修改如下：


1. DECLARE
2. -- all the backup files
3. @backup_file_list NVARCHAR(MAX) = N'
4. C:\Temp\TestDR_20171217@00:00_FULL.bak
5. C:\Temp\TestDR_20171217@01:00_LOG.trn
6. C:\Temp\TestDR_20171217@02:00_LOG.trn
7. C:\Temp\TestDR_20171217@03:00_LOG.trn
8. C:\Temp\TestDR_20171217@04:00_LOG.trn
9. C:\Temp\TestDR_20171217@05:00_LOG.trn
10. C:\Temp\TestDR_20171217@06:00_DIFF.bak
11. C:\Temp\TestDR_20171217@07:00_LOG.trn
12. C:\Temp\TestDR_20171217@08:00_LOG.trn
13. C:\Temp\TestDR_20171217@09:00_LOG.trn
14. C:\Temp\TestDR_20171217@10:00_LOG.trn
15. C:\Temp\TestDR_20171217@11:00_LOG.trn
16. C:\Temp\TestDR_20171217@13:00_LOG.trn
17. C:\Temp\TestDR_20171217@14:00_LOG.trn
18. '
19. ;

得到如下备份还原链条： 06.png

从这个结果，我们可以看到，系统选择了06:00这个差异备份文件，然后一步步使用接下来的事务日志备份文件进行还原。

最后总结

本期是SQL Server备份专题的第三期，在前两期基础上分享了如何通过备份文件查找SQL Server数据库备份链，并以此来还原数据库的理论方法和具体实现，可以帮助用户实现简单、快速查找数据库备份还原链条。

原文：http://mysql.taobao.org/monthly/2018/01/06/