How to tell if a backup log chain is broken?

Reference Reading / Similar Q&As

You might want to check out my answer that I posted in response to the question: Will VSS backups break logchain? (dba.stackexchange.com)

The explanation in my answer also links to the question How can I backup an SQL Server database using Windows Server Backup? (serverfault.com) which was also answered by myself.

Transaction Log Chain

When a Transaction Log (TLOG) backup is performed, the backup information is stored in the msdb database in various tables. The information stored will contain information like backup_type, logical_device_name, physical_device_name, is_copy_only, is_snapshot, and various ..._lsn columns (lsn = log sequence number).

You can retrieve the transaction log backup chain information from your SQL Server instance via the msdb database with the following script: 

/* ==================================================================
 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

Caution: The where clause currently select the AdventureWorks2012 database

Broken Transaction Log Chain

The (transaction) log chain is never broken unless one of the following conditions is met:

  • a Transaction Log backup file was deleted
  • a Transaction Log backup file is not accessible (somewhere in a backup device; 3rd-party backup solution)
  • the database is in SIMPLE recovery model
  • a Transaction Log backup was performed with the option TRUNCATE_ONLY
  • a FULL Database backup was taken without the COPY_ONLY option and was then deleted from disk because the developers only needed a quick backup to analyse a situation in the database and your FULL backup before that was deleted by (a) backup procedure.

Your Situation

In the screenshot you provided you have a FULL backup of the database that is is_copy_only and shortly after a FULL backup that is not is_copy_only. Now what you don't know:

Is the second FULL, non-is_copy_only backup a snapshot or not?

If you use my script from above and change the WHERE clause to match your database name, you might find out that that FULL backup that is not is_copy_only might just be a is_snapshot.

And that might just open up a new question:

Will the FULL, is_snapshot database backup of my database break the log backup chain?

But...

....whichever way this goes, as long as you have an unbroken chain of FULL and TLOG backups you can access, you can still restore your database to any point in time, even if you have another FULL backup in-between.

You can verify this with the output of my script for your database, by looking at the first_lsn and last_lsn numbers. They should match up, even when bypassing a FULL backup.

Better Be Safe Than Sorry

I have an AdminDB2 database on one of my instances. I created a TLOG backup, modified data, performed a FULL backup, modified data, performed a TLOG backup, ....

Lets have a look at my backup history of my 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

The order is date descending

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

The 3rd-party backups should be marked as is_snapshot = 1 and is_copy_only = 1. These backups will not conflict with additional backup steps/procedures performed using native SQL Server BACKUP DATABASE..., BACKUP DATABASE ... WITH DIFFERENTIAL.... and BACKUP LOG... statements. The 3rd-party database backups are not part of an existing backup set.

I hope this information is sufficient.

Tags:

Sql Server

Backup

Recent Posts