In my previous post, I explained the importance of the transaction log in SQL Server. This post looks at essential concepts you should understand if you are managing SQL backups.

Firstly, let’s see what kinds of backups SQL can perform.

Backup Types

The three typical types of backups are:

  • Full Database Backups – A complete copy of the database. The backup is usually compressed so that it is much smaller than the actual database.
  • Transaction Log Backups – A backup of all transaction log records created since the most recent transaction log backup or initial full backup. A series of transaction log backups create a ‘chain’.
  • Differential Backups – A backup of data changes (the delta) since the most recent full backup. Differential backups are much quicker to create than a full backup and require less space, but introduce an extra step to the recovery process.

The Restore Process

The three kinds of backups are used during recovery in this way:

  • Optional: back up the transaction log, if it is still intact. This is known as a tail-log backup
  • Restore the most recent full backup
  • Restore the most recent differential backup, if there is one. Skip any other differential backups that may have been taken
  • Restore the chain of transaction log backups since the differential backup, or since full backup, if no differential backup is available. Restore all the transaction log backups up to the most recent one
  • Optional: restore tail-log backup, if you took one

This figure from Microsoft illustrates the concept of recovery using full, differential and log backups:

If the transaction log file is intact, then you will have lost no data. If the transaction log is damaged or you chose not to take a tail-log backup, you will have lost the transactions that occurred since your last log backup.

Simple Recovery Model

This post has been about the default recovery model in SQL, which is called ‘Full’. There is another mode called ‘Simple’, which is sometimes appropriate to use with non-critical databases, such as a test environment.

In the Simple recovery model, transaction log backups are unnecessary. This saves disk space, but if the database is lost, you can only recover to the point of the last full backup. More information about recovery models is available here.

In January I will cover other backup and SQL management tips.

Further Reading