4

We have full backups and log backups configured.

During restore we apply full backup and subsequent log backups.

I know that if I take a manual full backup (non copy only mode) then it will break the differential chain. But in this example there is no differential backup. However there is transactional log chain. Will this chain be negatively affected due to the non copy only full backup?

7

If we just take your question and analyse the information stored in the msdb database, then the answer is: Yes, a BACKUP DATABASE ... with the COPY_ONLY parameter not set, will have an impact on the backup chain (history...).

This is because the database_backup_lsn (LSN = Log Sequence Number) which is stored together with the backup information is changed once a FULL BACKUP DATABASE... statement is executed.

Subsequent Transaction Log (TLOG) backups reference the FULL backup. This is displayed in the following picture:

![msdb - backup history data

The Log backup created at 2021-09-11 22:19:17.000 references the database_backup_lsn with the LSN 1221000000228000037 which is the first_lsn of the last FULL backup which occurred about a week ago.

The Log backup created at 2021-09-11 23:15:13.000 references the database_backup_lsn with the LSN 1221000000714400037, which is the first_lsn of the FULL database backup that occurred at 2021-09-11 22:34:32.000.

How does this impact your restore?

Well if you have an unbroken chain of TLOG backups available since a given FULL backup, then there is no impact whatsoever. You can restore your database with a FULL database backup and subsequent TLOG backup files up to any point in time.

However, if you have a broken chain of TLOG backups, then you would only be able to restore the database with a FULL backup and any available TLOG backups up until the missing TLOG files.

How to tell if a backup log chain is broken? (DBA Stackexhange answer)

Information

A FULL backup contains the consistent state of your database. (Caveat: You have to restore this file to prove that. Or a backup is only valid after it has been restored.)

A TLOG backup contains all the committed transactions since your last backup (FULL or TLOG). The backup contains a first_lsn and a last_lsn. As these are normally in a sequence, because the last_lsn of the previous TLOG backup is the first_lsn of the next TLOG backup, then you should have a consecutive sequence of TLOG backups that will allow you to restore the database to any point in time, provided you have an unbroken chain of TLOG backups.

COPY_ONLY Parameter

The COPY_ONLY parameter just tells SQL Server to perform a FULL backup, but to not reset the database_backup_lsn stored in the msdb backup history. All subsequent TLOG backups will be still based on the last FULL backup which was created without the COPY_ONLY parameter.

The SSMS GUI

Performing a FULL backup without the COPY_ONLY flag will change how the information is displayed in the GUI. The GUI normally display the last FULL, DIFF and TLOG backups required to bring the database back into a consistent state based on the time specified. This is because the GUI reads the msdb backup history information.

Script to Query msdb Backup History

I've added the script I use for your convenience.

/* ==================================================================
 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 
3
  • In the example screenshot you have provided, is how do you conclude that the tlog chain is broken or not.
    – variable
    Sep 30 at 3:51
  • The TLOG chain isn't broken. However, the TLOG after the FULL backup (going down in the list) has a new reference to the previously created FULL backup. This is why SSMS would display the FULL and the TLOG when you select 2021-09-11 23:15:13.000 as the restore time. The GUI won't skip past the FULL backup. However, you can use the previous FULL (not in screenshot) and all the TLOGs to create the same restore to 2021-09-11 23:15:13.000. That is why I stated: Well if you have an unbroken chain of TLOG backups available since a given FULL backup, then there is no impact whatsoever
    – John K. N.
    Sep 30 at 5:53
  • A FULL breaks the display in the SSMS Restore GUI for certain restore points, but not the actual ability of restoring a database using previous FULL backups and the unbroken chain of available TLOG backups. The other link How to tell if a backup log chain is broken? explains it better.
    – John K. N.
    Sep 30 at 5:56
8

A full (or differential) backup do not affect or break the log backup sequence. This is regardless of whether you specify COPY_ONLY or not.

It will influence the restore GUI and what backup it choses as a base for the restore sequence, though. But that is only a GUI thing.

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.