Printed 2017/07/24 08:52PM

A Full Backup can Impact a Log Backup–MCM Prep

By Steve Jones, 2011/02/02

In the early days of SQL Server you could not run a log backup while a full backup was running. In fact,you tried to schedule them apart from each other early in my career to prevent collisions. Nothing bad happens, but it does cause failures in your monitoring and those are annoying.

In modern versions of SQL Server, you can run log backups and simultaneous full backups. They don’t collide or block each other, but there is a way that the full backup impacts a log backup.

If you start a full backup, once all data pages are written to the backup device, the log records that were created since the start of the full backup are added to the full backup file. This allows the redo/undo process to complete and this gives you a full backup set that is intact as of the point in time in which the data pages finish writing.

If you run a log backup, typically the log records are written out, and then the VLFs in the log file that were written to disk are marked as inactive.

However since those log records are needed for the full backup to be complete, the log backup cannot clear those VLFs when the log backup finishes. That process still occurs, and technically this is part of the log backup, but it is deferred until the full backup completes.

Filed under: Blog Tagged: Backup/Recovery, mcm, sql server, syndicated
Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.