如何判断备份日志链是否损坏？

参考读物/类似问答

您可能想查看我在回答以下问题时发布的答案： VSS 备份会破坏日志链吗？(dba.stackexchange.com)

我的回答中的解释还链接到如何使用 Windows Server Backup 备份 SQL Server 数据库？(serverfault.com) 这也是我自己回答的。

交易日志链

执行事务日志 (TLOG) 备份时，备份信息存储在msdb数据库中的各种表中。存储的信息将包含backup_type、logical_device_name、physical_device_name、is_copy_only、is_snapshot和各种..._lsn列（lsn = 日志序列号）等信息。

您可以使用以下脚本通过 msdb 数据库从 SQL Server 实例中检索事务日志备份链信息：

/* ==================================================================
 Author......:  hot2use 
 Date........:  25.04.2018
 Version.....:  0.1
 Server......:  localhost (first created for)
 Database....:  msdb
 Owner.......:  -
 Table.......:  various
 Type........:  Script
 Name........:  ADMIN_Retrieve_Backup_History_Information.sql
 Description.:  Retrieve backup history information from msdb database
 ............   
 ............   
 ............       
 History.....:   0.1    h2u First created
 ............       
 ............       
================================================================== */
SELECT /* Columns for retrieving information */
       -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
       msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_start_date,
       msdb.dbo.backupset.backup_finish_date,
       -- msdb.dbo.backupset.expiration_date, 
       
       CASE msdb.dbo.backupset.type
            WHEN 'D' THEN 'Full'
            WHEN 'I' THEN 'Diff'
            WHEN 'L' THEN 'Log'
       END  AS backup_type,
       -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
       msdb.dbo.backupmediafamily.logical_device_name,
       msdb.dbo.backupmediafamily.physical_device_name,
       -- msdb.dbo.backupset.name AS backupset_name,
       -- msdb.dbo.backupset.description,
       msdb.dbo.backupset.is_copy_only,
       msdb.dbo.backupset.is_snapshot,
       msdb.dbo.backupset.checkpoint_lsn,
       msdb.dbo.backupset.database_backup_lsn,
       msdb.dbo.backupset.differential_base_lsn,
       msdb.dbo.backupset.first_lsn,
       msdb.dbo.backupset.fork_point_lsn,
       msdb.dbo.backupset.last_lsn
FROM   msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
            ON  msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
                
        /* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs    
        -------------------------------------------------------------------------------*/
WHERE  1 = 1
       
       /* ----------------------------------------------------------------------------
       WHERE statement to find Device Backups with '{' and date n days back
       ------------------------------------------------------------------------------- */
       -- AND     physical_device_name LIKE '{%'
       
       /* -------------------------------------------------------------------------------
       WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b 
       ---------------------------------------------------------------------------------- */
       -- AND     physical_device_name  LIKE '[fF]:%'                          -- STANDARD F: Backup Directory
       -- AND     physical_device_name  NOT LIKE '[nN]:%'                      -- STANDARD N: Backup Directory
       
       -- AND     physical_device_name  NOT LIKE '{%'                          -- Outstanding Analysis
       -- AND     physical_device_name  NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
       -- AND     backupset_name NOT LIKE '%Galaxy%'                           -- CommVault Sympana Backup
       
       
       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b 
       ---------------------------------------------------------------------------------- 
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  -- 7 days old or younger
       AND    (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE())  -- n days old or older
       
       */
       
       /* -------------------------------------------------------------------------------
       WHERE Statement to find backup information for (a) given database(s) 
       ---------------------------------------------------------------------------------- */
       AND database_name IN ('AdventureWorks2012') -- database names
       -- AND     database_name IN ('rtc')  -- database names
           
        /* -------------------------------------------------------------------------------
        ORDER Clause for other statements
        ---------------------------------------------------------------------------------- */
        --ORDER BY        msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause
           
        ---WHERE msdb..backupset.type = 'I' OR  msdb..backupset.type = 'D'
ORDER BY
       --2,
       
       2       DESC,
       3       DESC 

^{注意： where 子句当前选择 AdventureWorks2012 数据库}

交易日志链损坏

除非满足以下条件之一，否则（事务）日志链永远不会中断：

• 删除了事务日志备份文件
• 无法访问事务日志备份文件（备份设备中的某处；第 3 方备份解决方案）
• 数据库处于简单恢复模式
• 使用选项执行事务日志备份TRUNCATE_ONLY

你的情况

在您提供的屏幕截图中，您有一个FULL数据库的备份，is_copy_only并且在备份之后不久FULL，它不是 is_copy_only。现在你不知道的是：

第二个FULL，非is_copy_only备份是否是快照？

如果您使用上面的脚本并更改WHERE子句以匹配您的数据库名称，您可能会发现FULL不是的备份is_copy_only可能只是一个is_snapshot.

这可能会引发一个新问题：

我的数据库的FULL,is_snapshot数据库备份会破坏日志备份链吗？

但...

....无论哪种方式，只要您拥有完整的备份链FULL和TLOG可以访问的备份，您仍然可以将数据库恢复到任何时间点，即使您FULL之间还有另一个备份。

您可以通过查看first_lsn和last_lsn数字，使用我的数据库脚本输出来验证这一点。即使绕过FULL备份，它们也应该匹配。

安全胜过抱歉

我的AdminDB2一个实例上有一个数据库。我创建了TLOG备份，修改了数据，执行了FULL备份，修改了数据，执行了TLOG备份，......

让我们看看我的备份历史AdminDB2：

dbname    backup_start_date       backup_finish_date            type    Log   physical_device_name                                          C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-25 17:29:08.000 2018-04-25 17:29:08.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172908.trn   0   0   36000002022400042   36000002022400042   NULL    36000002021900001   NULL    36000002025100001
AdminDB2    2018-04-25 17:28:48.000 2018-04-25 17:28:48.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172848.bak   0   0   36000002022400042   36000002018900037   NULL    36000002022400042   NULL    36000002024200001
AdminDB2    2018-04-25 17:28:23.000 2018-04-25 17:28:23.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172823.trn   0   0   36000002018900037   36000002018900037   NULL    36000002021500001   NULL    36000002021900001
AdminDB2    2018-04-25 17:28:07.000 2018-04-25 17:28:07.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_172807.trn   0   0   36000002018900037   36000002018900037   NULL    36000002018400001   NULL    36000002021500001
AdminDB2    2018-04-25 17:27:32.000 2018-04-25 17:27:32.000 Full    NULL    C:\SQL\Backup\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak   0   0   36000002018900037   36000001990800037   NULL    36000002018900037   NULL    36000002020600001
AdminDB2    2018-04-25 17:15:00.000 2018-04-25 17:15:00.000 TLOG    NULL    C:\SQL\Backup\AdminDB2\TLOG\AdminDB2_TLOG_20180425_171500.trn   0   0   36000002016000003   36000001990800037   NULL    36000002018100001   NULL    36000002018400001

_{顺序是日期降序}

You can see the last TLOG backup at the top, the previous FULL (in-between) backup at 2018-04-25 17:28:48.000, the previous TLOG backup at 2018-04-25 17:28:23.000, and so on.

To restore the AdminDB2 database to the current point-in-time I would have to use the first FULL backup from 2018-04-25 17:27:32.000 (because I deleted the in-between FULL backup) together with all the TLOG backups.

Let's give that a try.

1. Delete the FULL backup file AdminDB2_FULL_20180425_172848.bak on my disk (or rename it), just because it is the one in-between.
2. Open up the Restore GUI in SSMS and add ..
   • the FULL backup AdminDB2_FULL_20180425_172732.bak
   • all the TLOG backup files
     • AdminDB2_TLOG_20180425_172807.trn
     • AdminDB2_TLOG_20180425_172823.trn
     • AdminDB2_TLOG_20180425_172908.trn
3. Make sure i set the option Overwrite the existing database (WITH REPLACE)
4. Perform the restore (or script the statement out into a query window)

Script

USE [master]
RESTORE DATABASE [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\FULL\AdminDB2_FULL_20180425_172732.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5, REPLACE
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172807.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172823.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [AdminDB2] FROM  DISK = N'C:\SQL\BACKUP\AdminDB2\LOG\AdminDB2_LOG_20180425_172908.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

Output

15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
75 percent processed.
90 percent processed.
100 percent processed.
Processed 848 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE DATABASE successfully processed 850 pages in 0.134 seconds (49.502 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.027 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.005 seconds (0.390 MB/sec).
100 percent processed.
Processed 0 pages for database 'AdminDB2', file 'AdminDB' on file 1.
Processed 2 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
RESTORE LOG successfully processed 2 pages in 0.005 seconds (3.125 MB/sec).

...and the database is back ONLINE.

Summary

The backup chain only breaks when you lose the TLOG backups in-between, other than that you can restore a database from a FULL backup a long time ago and just add all the TLOG backups.

However...

...it is faster to have a more recent FULL, DIFF and TLOG backups handy.

_{Additional information in response to comment: Transaction Log backup was performed with the option TRUNCATE_ONLY - when this happens, is there any way to know this by T-SQL query}

Backing Up Transaction Log With Truncate_only

In previous versions of SQL Server prior to SQL Server 2008 you could use the following statement:

 BACKUP LOG [AdminDB2] WITH TRUNCATE_ONLY

This has been deprecated and is no longer supported. You will receive an error message like the following:

Msg 155, Level 15, State 1, Line 10
'TRUNCATE_ONLY' is not a recognized BACKUP option.

The new method is to backup to disk NUL and is performed with the following command:

BACKUP LOG [AdminDB2] TO DISK='NUL'

This will return the following information:

Processed 1 pages for database 'AdminDB2', file 'AdminDB_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.001 seconds (1.464 MB/sec).

Advisory
Do NOT use this in production. You will lose the ability to restore to a point-in-time during that TLOG backup.

Reference: BACKUP (Transact-SQL) (Microsoft Docs)

Your backup history will show this as:

dbname      backup_start_date       backup_finish_date      type ldev  pdev C   S   checkpoint_lsn   dbase_backup_lsn     dlsn  first_lsn           flsn    last_lsn
AdminDB2    2018-04-26 09:35:05.000 2018-04-26 09:35:05.000 Log NULL    NUL 0   0   36000002030100002   36000002022400042   NULL    36000002033400001   NULL    36000002033700001

The information for the logical_device_name (ldev) and physical_device_name (pdev) will both contain the value NULL. This is a sign that a BACKUP LOG ... was performed with a TRUNCATE_ONLY (new: TO DISK='NUL'). You will have lost the ability to restore past this point using Transaction Log backups.

_{Additional information in response to comment: Yes - this was a is_snapshot = 1 [backup]}

is_snapshot

Please read the section is_snapshot in my answer to the question Use of third-party VSS backup plus native SQL backup

From my original answer:

If the database backup history has the flag is_snapshot set to 1 then you know that this backup was performed using a 3rd-party software that triggered the SQL Server Writer (VSS Service for SQL Server) which allowed the 3rd-party software to backup the database almost instantaneously.

From the official documentation on what Snapshot Backups are:

SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

Reference: Snapshot Backups (Microsoft Technet)

A backup created using this feature can also be restored almost instantaneously.

Summary

第 3 方备份应标记为is_snapshot = 1和is_copy_only = 1。这些备份不会与使用本机 SQL Server和BACKUP DATABASE...语句执行的其他备份步骤/过程发生冲突。第 3 方数据库备份不属于现有备份集。BACKUP DATABASE ... WITH DIFFERENTIAL....BACKUP LOG...

我希望这些信息是足够的。

根据 MSDN 文档TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths连续的T-Log备份序列由 a 绑定Log Chain，它以 FULL 备份开始。现在，除非我们明确运行任何破坏日志链的操作（例如，运行 BACKUP log TRUNCATE_ONLY* 或切换到 SIMPLE 恢复模型），否则现有链保持不变。在日志链完好无损的情况下，您可以从媒体集中的任何 FULL 数据库备份恢复数据库，然后将所有后续T-Log备份恢复到故障点。

并且作为这里MSSQLTIPS的文档 恢复数据库时，初始数据库RESTORE序列必须从 FULL 数据库备份开始。数据库 RESTORE 序列不能以差异文件备份或事务日志备份开始。还原数据库时，有四个重要的 LSN：、和. 可以使用RESTORE HEADERONLY命令从 SQL Server 备份文件中检索这些值。FirstLSNLastLSNCheckpointLSNDatabaseBackupLSN

例如

在上面的屏幕截图中，我想向您展示“完整备份”标题和“事务日志备份”标题。如果Backup type 为1，表示它是完整备份的header 部分。如果有2则表示 ，即事务日志备份标头。

在此屏幕截图中，我想首先向您展示 Restore Headeronly.. 完整备份，然后是事务日志备份，然后是同一数据库的完整备份。

注意：出于安全原因，我在这里突出显示了屏幕截图中的某些部分。

供您进一步参考这里和这里

阅读您的问题后，我不相信您的“日志链”由于此Appsure备份而损坏。假设您可以恢复在第 5 行进行的FULL备份，那么您应该能够毫无问题地恢复在第 6 行进行的备份。APPSUREWITH NORECOVERYDIFFERENTIAL

我相信你真正的问题是：

我如何确定是否在我不知情的情况下进行了“流氓”非仅复制备份。FULL

可能有更复杂的方法来确定这一点，但也许一个简单的查询来检查存储在您不期望的位置上的非仅复制备份就足够了。

从您的屏幕截图来看，您的正常备份似乎存储在E:\SQLBackups. 运行一个简单的查询来检查FULL存储在其他地方的非仅复制备份可能就足够了。

SELECT s.database_name
    ,m.physical_device_name
    ,s.backup_start_date
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
    AND s.is_copy_only = 0
    and physical_device_name not like 'E:\SQLBackup%'
ORDER BY backup_start_date DESC