Sql-server – Is the restore process being badly affected by this third-party non copy-only backup

backuprestoresql servertransaction-logvss

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?

Best Answer

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!