Backups Causing Transaction Log Growth In Simple Mode

Andy Mallon explains why the transaction log will grow during a backup even if you’re in simple recovery mode:

When SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. Upon restore, it will then roll those transactions forward or backward, as necessary, to ensure a consistent image is restored.

In our librarian metaphor, she would keep an activity log, which would include the changes to books A and D from the first update, then also the changes to D, X, Y, and Z from the second update. She would not “fix” the data within the backup, but simply store those update details along with her mashed-up copy. In the unlikely event she had to recreate the books (ie, a restore), then she would go back and spend the effort to piece it back together. During that restore process, she would look at the first transaction and see that her copy of Book A in her backup was too old, but Book D already had the update, and she would roll forward the update to Book A. Next, she would process the second update and see that Books X, Y, and Z had the updates, but D still needed this second update, and she would roll forward that second update to Book D. At this point, she would have successfully reconstructed an image that is consistent to the time the backup completed.

Great metaphor to describe consistency during backups.

Related Posts

Using The Restore-DbaDatabase Pipeline

Stuart Moore describes the updated Restore-DbaDatabase cmdlet: The biggest change is that Restore-DbaDatabase is now a wrapper around 5 public functions. The 5 functions are: Get-DbabackupInformation Select-DbabackupInformation Format–DbabackupInformation Test–DbabackupInformation Invoke-DbaAdvancedRestore These can be used individually for advanced restore scenarios, I’ll go through some examples in a later post. Stuart then provides additional information at the […]

Read More

Backup-Related Instance Settings

Monica Rathbun explains a few instance-level backup properties: Default backup media retention in days. Now the first things that comes to my mind is that “hey this is a cleanup job” SCORE! Thinking that maybe this will auto delete old backups. After all isn’t that what retention means? NOPE, not in this case. In this case […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930