The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

In today’s post I want to introduce you to the three recovery models a database can use, and to the various kinds of backups that are possible with SQL Server.

Before that, I need to explain a little bit about the transaction log that exists in each database. Whenever a transaction makes a change to a database, a record of the change is made in the transaction log, using log records to describe the change. Each transaction that’s making changes can either commit, making those changes durable in the database, or it can roll back, which removes the effects of the changes from the database.

If the system crashes, databases undergo crash recovery, which makes sure that:

  • For all transactions that were committed at the time of the crash, their effects are present in the database. This may mean that some log records are redone – re-applying the described changes to the database.
  • For all transactions that were not committed at the time of the crash, their effects are not present in the database. This may mean that some log records are undone – applying changes that reverse the described changes in the database (essentially doing the anti-operations, like deleting a table row that was inserted by an uncommitted transaction).

Crash recovery makes sure that the database is transactionally-consistent. The same set of operations occur when a database is restored as part of disaster recovery, using the transaction log records present in your various backups.

The transaction log needs to be managed to ensure it doesn’t grow out of control, by periodically allowing the contents of the log to be cleared, so the space can be reused for more log records. “Clearing” the log is sometimes referred to as “truncating” the log and they both mean the same thing: some portion of the log will be marked as available for reuse.

Back in 2009 I wrote an long, introductory article for TechNet Magazine that discusses all of this in more detail – see Understanding Logging and Recovery in SQL Server – and if you want really comprehensive information on the transaction log, see my 7.5 hour, online Pluralsight course: SQL Server: Logging Recovery, and the Transaction Log.

The way this log clearing is triggered depends on the recovery model that is used for the database.

Recovery models

There are three recovery models:

  • Full recovery model (the default and the most commonly used)
    • All modifications in the database a fully logged
    • The log will not clear until a transaction log backup is performed
  • Bulk-logged recovery model
    • Some modifications (like an index rebuild or a bulk load) can be minimally logged, which reduces the amount of log records generated
    • The log will not clear until a transaction log backup is performed, just like the full recovery model
  • Simple recovery model
    • Some modifications can be minimally logged, just like the bulk-logged recovery model
    • The log will not clear until a checkpoint occurs (usually automatically), and transaction log backups are not possible

The main thing to remember is that if your database uses the full or bulk-logged recovery model, you must perform periodic transaction log backups or the transaction log will grow forever.

Backup types

There are three commonly used backup types in SQL Server:

  • Full database backup
  • Transaction log backup
  • Differential database backup

There are also smaller-granularity data backups that are much less commonly used:

  • Full filegroup backup
  • Full file backup
  • Differential filegroup backup
  • Differential file backup

I’m going to focus on the three common backup types, as these are the ones you’ll be using as an Accidental DBA.

Full database backup

A full database backup (usually just referred to as a ‘full backup’, skipping the word ‘database’) backs up the entire database, and a full backup must be performed before you can perform other types of backups. When you’re performing a restore operation, the first backup you restore from must be a full backup, as that is the basis for other backups to be restored.

A full backup backs up all the data from the data files plus some transaction log records from the transaction log. This is to ensure that when restoring the full backup, the transaction log records allow the restored database to be brought to a transactionally-consistent point. Contrary to popular myth, full backups to not allow the transaction log to clear, and in fact do not affect the transaction log in any way whatsoever.

A full backup allows you to restore the database to a single point in time – the end of the backup – or to continue restoring more differential or log backups to bring the restored database more up-to-date.

Full backups are usually performed once per day or once per week.

Transaction log backup

A transaction log backup (usually just referred to as a ‘log backup’, skipping the word ‘transaction’) backs up all the transaction log records generated since the most recent log backup occurred. When a log backup completes, log clearing will be performed, which allows the log to be reused, helping to prevent log growth. A log backup cannot be performed until a full backup has been performed, but once a full backup has been performed, log backups and full backups do not interact or have any effect on each other at all.

A log backup allows to you continue a restore operation started by restoring a full backup, with each log backup that you restore bringing the restored database more up-to-date.

Log backups are usually performed once or multiple times per hour.

There is a special kind of log backup called a ‘tail-of-the-log’ or ‘tail-log’ backup that is performed when recovering from a disaster. The idea is that the very first thing you do after a disaster occurs is to take a final log backup, capturing all the transaction log that was generated since the most recent scheduled log backup before the disaster. During a restore sequence, this log backup will be the last one to be restored, bringing the database as close as possible to the point of the disaster.

Differential database backup

A differential database backup (usually just referred to as a ‘differential backup’ or ‘diff backup’, skipping the word ‘database’) backs up all the data that has changed since the most recent full backup. As more and more data changes in the database since the most recent full backup, successive differential backups will get larger and larger. Differential backups are cumulative, not incremental (which is a common misconception, as other database systems do have incremental data backups – SQL Server does not).

Differential backups are used to speed up restores by essentially replacing a number of log backups, as a differential backup is essentially the net effect of all the log backups since the most recent full backup.

Differential backups are not so commonly used, but when they are, they are usually performed once or multiple times per day.

Summary

Most of you will be using the full recovery model, which means you need to perform log backups regularly. But how regularly? A proper backup strategy takes into account the RTO and RPO requirements to determine what backup types to use, and how often to perform them. And that’s the topic for tomorrow’s post!

14 thoughts on “The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types

  1. Great stuff here Paul!

    Question for ya: is it OK to allow a log backup to run while a FULL backup is being taken?

    Scenario:
    Let’s say we’ve implemented Ola’s backup scripts – 1 full backup per day followed by 30 minute log backups. The FULL backup starts at 2:00 AM and takes at least 45 minutes to complete. Log backups run at the top and bottom of the hour. In this scenario, we have overlap. At 2:00 AM, the FULL backup and the LOG backup are started and running simultaneously. Then, at 2:30 we have a case where a FULL backup is running and a LOG backup is initiated.

    I would answer that is does not matter. However, the 2:00 AM FULL and 2:00 AM LOG backups make me nervous. What’s the scoop?

    Many Thanks!
    Mark

      1. Prior restore tests revealed no impact yet I still had doubts. Thanks for the response and clarification, most appreciated.

  2. One question, what happens if you have a full backup but not log backups? You can restore full backup and then restore the tail log backup or do you need a Transaction log backup first?

    1. It depends. If you’re in the simple recovery mode, you can’t do log backups at all. In either full or bulk-logged, if you’re only doing full backups, your log is going to grow forever and you must to log backups.

  3. Hi Paul,

    Regarding the transaction log backup, I have some confusion.

    Say in Bulk logged recovery mode, a transaction log backup after some bulk operation(minimally logged) would contain the log portion(extent allocation changes) and data portion(changed extents tracked by BCM(Bulk Changed Map)), am I right?

    If so, what would happen to the mixed extents, say, my bulk operation imports data into a table and finally the table takes less than 8 pages, which means bulk operation doesn’t get any dedicated extents’s allocated. The mixed extents can contain the data pages, IAM and maybe some pages belonging to other entities.

    Are the pages allocated to the table or the whole mixed extents get involved in above backup? Are mixed extents treated the same way as dedicated ones in the above backup?

    Thanks in advance.

      1. Sorry to not make my question clear.

        My question has 3 parts
        1. a transaction log backup after some bulk operation(minimally logged) would contain the log portion and data portion
        2. say in 1, the data portion means extents allocated in bulk operation to table
        3. say extents in 2, if the table finally has less than 8 pages, no dedicated extent got allocated in
        bulk, does the backup in 1 back up the mixed extents or only the pages in then mixed extents?

        Thanks

  4. Hi Paul,

    Thanks for presenting a Nice article…I have gone through the Backup methodologies,Shrink Methodologies but I still have questions…..

    1) I am going to delete around 10 millions of records (in total) from several tables…. Our Database is using FULL Recovery mode and noticed that Transaction Log size is already 14 GB. Does deleting 10 millions of records makes the transaction log to grow further ? Does it impacts Performance ?

    Kindly suggest the ways to cleanup/delete 10 millions of records withoug growing transaction Log size ?

    Thanks & Regards,
    Pradeep

    1. If you have the table partitioned, you can drop a partition, but I don’t think you do. You need to delete the records in small batches and back up the transaction log between batches, so you don’t have one large transaction that deletes all 10 million records. Recovery model doesn’t make any difference to this.

  5. Hi Paul,

    Nice Article.
    could you please elaborate below sentence ?
    Simple recovery model:
    Some modifications can be minimally logged, just like the bulk-logged recovery model.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.