5

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. enter image description here

  • Did you checked through (Restore Headeronly ...) from there it will confirm your log chain sequence. As per attached screen shot by you, it's just showing the Backup size with media name. – Md Haidar Ali Khan Apr 25 '18 at 10:33
5

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.

  • Nice explanation with reference. +1 – Md Haidar Ali Khan Apr 25 '18 at 15:59
  • Thank you for much more detailed information. Question: Transaction Log backup was performed with the option TRUNCATE_ONLY - when this happens, is there any way to know this by T-SQL query? – Santhoshkumar KB Apr 26 '18 at 6:14
  • 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. Yes - this was a is_snapshot = 1 – Santhoshkumar KB Apr 26 '18 at 6:20
1

As per MSDN documentation TRANSACTION LOG BACKUP and RESTORE SEQUENCE: Myths & Truths A continuous sequence of T-Log backups is tied by a Log Chain, which starts with a FULL backup. Now, unless we run anything explicitly that breaks the log-chain (Ex., running BACKUP log TRUNCATE_ONLY* or by switching to SIMPLE recovery model), the existing chain remains intact. With the log chain intact, you can restore your database from any FULL database backup in the media set, followed by all subsequent T-Log backups to the point of failure.

And as MSSQLTIPS documents here When restoring a database, the initial database RESTORE sequence must begin from a FULL database backup. A database RESTORE sequence cannot begin with a differential file backup or transaction log backup. When restoring databases there are four important LSNs: FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN. These values can be retrieved from a SQL Server backup file using the RESTORE HEADERONLY command.

For Example

enter image description here

In the above screen shot I want to show you "Full Backup" Header and also "Transaction log Backup" header. If the Backup type is 1 , which means it is a header part of full backup . And if there is 2 that means , which is transaction log backup header.

enter image description here

In this screen shot I want to show you first Restore Headeronly.. for Full Backup Then Transaction log Backup and again Full backup of the same database.

Note: Here I have highlighted some part in the screen shot due to security reason.

For your further ref here and here

  • Thanks for noting this you can restore your database from any FULL database backup in the media set, followed by all subsequent T-Log backups to the point of failure. – Santhoshkumar KB Apr 25 '18 at 13:23
  • Also, the AppAssure was indeed doing the TRUNCATE LOG when it took a non COPY_ONLY backup !! – Santhoshkumar KB Apr 25 '18 at 13:24
1

After reading your question, I'm not convinced your "log chain" is broken because of this Appsure backup. Assuming you can restore the FULL backup taken by APPSURE at line 5 WITH NORECOVERY, you should be able to restore your DIFFERENTIAL backup taken at line 6 without any problems.

I believe your real question is:

How can I determine if 'rogue' FULL non-copyonly backups are being taken without my knowledge.

There might be more sophisticated ways of determining this, but perhaps a simple query to check for non-copyonly backups being stored on a location you were not expecting would suffice.

From your screenshot, it looks like your normal backups are being store at E:\SQLBackups. It might be sufficient for you to run a simple query to check for FULL non-copyonly backups being stored somewhere else.

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
  • The AppAssure backups up to some location and the meeting with IT team went like this - they can provide us only the mdf and ldf files which is attachable. So we cannot have it in NORECOVERY mode for further LOG restores. Thanks for the idea on the script to verify this and alert me whenever this happens. – Santhoshkumar KB Apr 25 '18 at 13:17
  • 1
    Well - I guess you could still check for backups being created on locations that are not expected - that's the idea of the query I provided in my answer. – Scott Hodgin Apr 25 '18 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.