Restore through FULL backup

Problem

A common misconception that i've heard a few times around backups is that a FULL backup invalidates previous TRANSACTION LOG backups. By this, I mean, that when a FULL backup is taken, TRANSACTION LOG backups cant be restored past that FULL backup.

Solution

I have created two SQL Agent jobs, one to take a FULL backup every hour and the other a TRANSLOG backup every 10 mins. Also, i've created a job that inserts a CURRENT_TIMESTAMP into a table called TIMES every minute.

Details of the backups are held in the table dbo.backupset in msdb.  Here you can see what type of backup was taken, the datetime of the backup plus many other details pertaining to the backup.

Some of the most interesting columns are the ones containing the LSN (log sequence values) values.

Books online has the following description for LSNs...

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The first_lsn and last_lsn columns show the first and last values of the LSN contained in the backup file when the backup was taken.   It noticeable that the last_lsn of the previous TRANSLOG  backup is identical to the first_lsn of the next TRANSLOG backup.  Its also noticeable that the last_lsn of the TRANSLOG backup taken before the FULL backup is identical to the first_lsn of the TRANSLOG backup taken after the FULL backup.

Testing

To demonstrate how the restore works, I will restore the following backups...

• The FULL database backup from 20160916 @ 3pm
• 6 TLOG backups from 3.05pm – 3.55pm
• 1 TLOG backup from 4.05pm (taken after the 4pm FULL database backup)

Backups taken between 3pm and 4pm and restored with NORECOVERY

and finally, the backup taken at 4.05pm, after the FULL backup, with the database recovered.

Now, when checking the database, you can see that the TIMES table has been recovered to 4.05pm, straight through the FULL backup of 4pm.

Conclusion

As shown, the latest FULL database backup isn't needed to restore a database to a point in time. BUT...don't take this as a green light to stop taking FULL database backups altogether. As the amount of TLOG backups that need to be restored increases, so do your problems. Each TLOG restore will add more time to the restoration process. There could come a point where having hundreds or thousands of TLOG backups to restore will push the restoration duration past your recovery time objective (RTO). Also, the greater number of files required means a greater chance of file corruption or an accidental file deletion by a low caffeined DBA. My recommendation would be to make an hourly or nightly FULL backup with TLOG backups in between.

Bonus Cup

Differential backups work in the same way. You can restore TLOG backups through them also. Its worth investigating whether you can benefit by using DIFFERENTIALs, especially on databases that don't change much, as they can really reduce the time and complexity to bring a database back to a point of failure (imagine having to restore 23 hours worth of TLOG backups taken every 5 mins – 23x12= 276 TLOG backups to restore)

Something to be aware of is the column “Begins_log_chain” within the backupset table. When this value equals 0 then there's no problems with TLOG restores going straight through. When this value is 1, then something has happened which has broken the log chain, which will require a FULL backup to reinitialise. In my experience, this is usually because the database has been flipped from FULL to SIMPLE and then back to FULL recovery model.

Now, trying to restore past the TLOG backup with the begin_log_chain = 1 will not be possible and an error will be returned...

...when restoring the 1.05pm TLOG backup.