Is my restore process being badly affected by this third-party non copy-only backup?

Question

This question may read like a duplicate, but is situation-based, and is posted from confusion applying the knowledge from other answers.

I've read dozens of articles (among 1, 2, 3, 4), but am finding conflicting opinions (based on my understanding, which is now suffering from information overload, or perhaps not including enough information in my other questions). I am creating this question therefore to get a definitive answer based on my situation.

Given the following backup scenario, I need to know whether the third-party backup software would prevent me from performing a full recovery to the point of the latest backup (18:00)?

Time  | Action                                       | Device   
------|----------------------------------------------|----------------------------
12:00 | Full backup (non copy_only)                  | D:\MyBackupDevice                 
13:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
14:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
15:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
16:00 | Full backup (non copy_only) VSS snapshot     | Third-party off-site device
17:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                
18:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
19:00 | Disaster strikes                             |                                   

My restore goal is to restore to the point of the 18:00 backup (I know there is a tail-log backup that can be added in as well to get the remaining transactions up until the disaster, but let's keep things simple for now).

Based on this answer, I believe that the third-party backup causes a conflict with my own transaction log restoration (as per this answer), which prevents me from performing my restore to that point. My understanding is that the transaction log backup contains data since the last full non copy_only backup.

Is this correct? Does the third-party backup prevent my own restore routine from working because it is a non copy_only backup?

Answer 1

Maybe this will help you understand. Let's do a demo! We'll make a dummy database with some dummy data.

USE master;

/*Create a dummy database*/
CREATE DATABASE LogRestoreTest

/*We full now*/
ALTER DATABASE LogRestoreTest SET RECOVERY FULL

/*Context is everything*/
USE LogRestoreTest

/*If nothing changes, do we even need a log backup?*/
CREATE TABLE dbo.t1 (Id INT)

Now we'll take one full backup. Just one. Promise.

/*Take a full backup, dummy*/

BACKUP DATABASE LogRestoreTest 
TO DISK = 'F:\Backup\LRT_FULL.bak' 
WITH INIT, FORMAT, COMPRESSION

Now we'll make some changes and take some log backups. Just like real life. It's fun. No drinking yet.

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a log backup*/
BACKUP LOG LogRestoreTest 
TO DISK = 'F:\Backup\LRT_LOG_1.trn' 
WITH INIT, FORMAT, COMPRESSION

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another log backup*/
BACKUP LOG LogRestoreTest 
TO DISK = 'F:\Backup\LRT_LOG_2.trn' 
WITH INIT, FORMAT, COMPRESSION

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

Now we'll take an 'out of band' full backup, and another log backup.

/*A second full backup appears!*/
BACKUP DATABASE LogRestoreTest 
TO DISK = 'F:\Backup\LRT_FULL_2.bak' 
WITH INIT, FORMAT, COMPRESSION

/*Take another log backup*/
BACKUP LOG LogRestoreTest 
TO DISK = 'F:\Backup\LRT_LOG_3.trn' 
WITH INIT, FORMAT, COMPRESSION

If I want to restore data to the third log backup, I have two options.

Restore the first full backup, and all three log backups:

/*Restore the full backup*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_FULL.bak' 
WITH REPLACE, NORECOVERY


/*Square one*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_1.trn' 
WITH NORECOVERY

/*What about to here?*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_2.trn' 
WITH NORECOVERY

/*Obligatory Your Mom*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_3.trn' 
WITH NORECOVERY

Or I can restore the most recent full and then the final log file:

/*Restore the full backup*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_FULL_2.bak' 
WITH REPLACE, NORECOVERY


/*What happens if I try to jump the restores?*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_3.trn' 
WITH NORECOVERY

You'll have to forgive me not explaining differential backups here, but there are plenty of resources out there that cover DBA 101 material.

Hope this helps!

Answer 2

_{Community wiki answer:}

In your previous question, you said:

... it emerged that the tool used non copy_only backups, thus destroying the log chain

A FULL backup never breaks the log chain.

A non-COPY_ONLY FULL backup just resets the differential base (the reference point for a DIFFERENTIAL backup).

So, no, the 16:00 | Full backup (non copy_only) VSS snapshot would not prevent you successfully performing the following restore sequence:

1. 12:00 | Full backup (non copy_only)
2. 13:00 | Tran log backup (non copy_only)
3. 14:00 | Tran log backup (non copy_only)
4. 15:00 | Tran log backup (non copy_only)
5. 17:00 | Tran log backup (non copy_only)
6. 18:00 | Tran log backup (non copy_only)

---

There's an example restore sequence using the not-latest FULL backup here:

• Apply Transaction Log Backups (SQL Server)

A LOG backup contains data since the last LOG backup. FULL and DIFFERENTIAL backups do not truncate the log.

From The Transaction Log (SQL Server)

Under the full recovery model or bulk-logged recovery model, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).

Answer 3

I'm going to consolidate the information from my previous answers into one single answer here to elaborate a bit on what happens in your scenario. I would like to point out that the prerequisites in your questions have changed slightly from question to question.

TL;DR

No, the regular FULL snapshot backup of the 3rd-party solution from your example will not break the backup chain.

---

Baseline

The baseline for the explanation will be your table as defined in your question:

Time  | Action                                       | Device   
------|----------------------------------------------|----------------------------
12:00 | Full backup (non copy_only)                  | D:\MyBackupDevice                 
13:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
14:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
15:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
16:00 | Full backup (non copy_only) VSS snapshot     | Third-party off-site device
17:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                
18:00 | Tran log backup (non copy_only)              | D:\MyBackupDevice                 
19:00 | Disaster strikes                             |           

Explaining The Backup

Each time SQL Server performs a backup of the database it will insert the relevant Loq Sequence Number (LSN) information into the msdb backup history, so that in case of a restore the process knows which files (backup sets) it requires to restore the database. It stores the backups sequence with the help of the LSNs in the columns first_lsn , last_lsn and the database_backup_lsn (and some other columns).

This information is also stored in the backup files themselves. These columns assist the restore process when restoring a database from a FULL backup and additional TLOG backups.

The first_lsn will contain the LSN of the first transaction stored in the TLOG backup and the last_lsn will contain the LSN of the last transaction in the TLOG backup.

The database_backup_lsn will contain the LSN of the last FULL Backup.

Stop. Go back. Read this section again.

Example of LSN numbers from a similar situation

backup_type checkpoint_lsn      database_backup_lsn first_lsn           last_lsn             
Full        35000001911700042   35000001908000042   35000001911100002   35000001913500001    
Log         35000001911700042   35000001911700042   35000001911200001   35000001914300001    
Log         35000001911700042   35000001911700042   35000001914300001   35000001914600001    
Log         35000001911700042   35000001911700042   35000001914600001   35000001914900001    
Log         35000001911700042   35000001911700042   35000001914900001   35000001915200001    
Full        35000001915700042   35000001911700042   35000001915700042   35000001917500001    
Log         35000001915700042   35000001915700042   35000001915200001   35000001918300001    

As you can see in this example the LSN of the TLOG backups always match up. The last_lsn of a previous TLOG backup matches the first_lsn of the next TLOG backup.

However, the FULL backup has an LSN that begins inside the LSN range of the next TLOG backup and ends inside the LSN range of the same TLOG backup.

Restore based on your example table

If you were required to restore your database to 18:00 because of an error at 19:00 then you would require the FULL backup from 12:00 and your Transaction Log backups from 13:00 through 18:00.

The LSNs would be without interruptions. No need for the 3rd-party FULL Backup.

You can verify this by checking the first_lsn and last_lsn column of your current backups. They should match each other.

End of Explanation

You could end the explanation here and now, but there are considerations to be made regarding the 3rd-party solution and the solution you are using for your backups.

(To be continued...)

Reference: SQL Server - Understanding SQL Server Backups (Paul S. Randal)