HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlModerate

How to tell if a backup log chain is broken?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlsql-serverstackoverflowdbabackup
logbrokentellbackuphowchain

Problem

I had a situation where the Native Backups were being made on a Server.

I happened to see in msdb that there was a third party backup tool (AppAssure) that was also taking VSS (kind-of) backup to virtual device.

At some interval, the AppAssure (backup being made to VIRTUAL DEVICE) was doing a COPY_ONLY backup and at some other interval it was doing a FULL backup breaking the log chain.

Is there any way(T-SQL query) to know when a backup log chain is broken?

Here is a screenshot of the situation from February.

Solution

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 ('Adven

Code Snippets

/* ==================================================================
 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_dev
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
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
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).
BACKUP LOG [AdminDB2] WITH TRUNCATE_ONLY

Context

StackExchange Database Administrators Q#204883, answer score: 13

Revisions (0)

No revisions yet.