Pages

Wednesday 13 July 2011

SQL 2008 Backup Strategies

For some reason I have found the SQL recovery models and backup strategies a strange mix of being confusing but interesting. To help understand the subject of backup/restore and SQL’s different recovery models I thought I’d share my understanding with the world at large. Maybe if anyone out there is reading this you can contribute as well.

The backup strategy you use depends on a variety of recoverability considerations:

  • What is the level of transaction volume. Does the database change minute-by-minute or say hour-by-hour?
  • What is considered to be an acceptable recovery time?
  • What is considered an acceptable level of data loss? Maybe you need to return to an exact moment in time.
  • How big can a backup be?

A backup strategy will require you to make decisions as to which kind of backup to make. And there are several. So you will need a fundamental understanding of these backup types.

Full Database Backup

A full database backup will truncate the transaction log and then copy every remaining data page and transaction log page to the backup media. The transaction log truncation will be non-reorganizing, meaning that no attempt is made to defrag/compact the log. It is simply truncated to the point of the last required transaction. Most backup strategies require a full database backup as the baseline for recovery. Remember that the log file will be truncated! From what I've read there is little point on having multiple log files. Keep just one. Also place the transaction log on a separate physical structure from the database. That way a loss of the disk containing the data files will not affect the log file. This may also help performance as log files are written to sequentially. Also, use RAID 1 so the log will be available in case of device loss. Regular backups will mean that not only will the log file not get too big but this will help prevent fragmentation.

Differential Backup

A differential backup will store all of the database pages that have been modified since the last full database backup. Note that this is a true differential backup and not an incremental backup. This means that each differential backup is inclusive of all transactions executed since the last full database backup and not simply since the last differential backup.

File or Filegroup Backup

If you are dealing with a very large database, you can back up individual files or filegroups. Breaking a large database into files or filegroups for backup allows you to back up portions of it on a rotating schedule when it might be too time-consuming to back up the entire database at once. If there is a failure affecting only one file or filegroup, only that portion and subsequent transaction logs would need to be restored. The log file is not in a filegroup.

Transaction Log Backup

This backup type will perform a non-reorganizing backup of the transaction log and store the transactions to the backup media. The backup types mentioned above store copies of the data pages at a particular time. This type of backup stores the actual transactions statements. When you restore using the full or differential backups, using the transaction log backup as well will involve replaying (if that’s the right word?) or re-executing the transactions on the log backup again which would be written back to the database. This process could take some time.

Recovery Models

Now that we understand (I hope) the different backup types, you need consider SQL’s three recovery models. Recovery is all about how the log file is treated by the SQL server on a day-to-day basis and what is made available for backup. Remember its all about the log file! Recovery in this context is about the level of logging and log retention.

You can determine the recovery model (which by default will be full) in the following way:

  1. Connect the SQL Server Management Studio to the correct instance hosting the database
  2. Expand the Databases folder and locate the right database
  3. Right click the database and select Properties
  4. Select Options
  5. Decide on the recovery model by selecting Recovery Model

recoverysql

 

You can alter the recovery model using the following statement:

alter database TESTDB
set recovery Full

You should realize that the recovery model you choose will impact on the backup method you choose.

Simple

Simple means that the log file will be truncated each time the data pages and log pages held in RAM are flushed are written to disk (checkpointed).  This keeps the log file small (there’s no point in backing up the log, in fact you can’t) which is good. But, you will not be able to recover to a point in time. You wouldn’t usually use this one. You might if it was a read-only database or you were developing a database application perhaps.

Bulk-Logged

The bulk-logged recovery model uses less disk space than a full logging solution by performing minimal transaction logging for the following operations:

  • SELECT INTO
  • bulk-load
  • CREATE INDEX
  • All operations involving text and image data types

A database that is in bulk-logged recovery mode cannot be recovered to a specific point in time if a bulk transaction has occurred. You still require log backups. A bulk insert (where you might be inserting a million rows into a table) would cause the log file to become very large if every transaction was recorded (if in Full mode) and would have performance implications. So you can switch to bulk logged from full just before the bulk operation. Once complete you set the recovery model back to full.  The bulk operation would be logged as a kind of summary statement. So every transaction would be recorded while being in Full mode, then a summary of the bulk operation and then a continuation of all transactions when in Full mode again. What about the point-in-time recoveries? If the database is in the bulk-logged recovery model and no bulk actions have occurred since the last full backup, the database can be restored to a point in time. If, however, a bulk action has occurred, it can only be fully restored. So, it minimally logs bulk transactions but fully logs other transactions.

Full

The full recovery model is what you would use most of the time. It will give you the best recoverable opportunity at the expense of logging overhead however. Microsoft recommend that you use this model over the other two. The full recovery model will log every transaction to the log and is persistent after a checkpoint. A transaction is a change and any change on the database will cause an entry to be added to the log! A read does not cause a change so this of course will not cause an entry to be made to the log.

 

OK, so we are happy with the different backup types and with the different recovery models. The recovery models really describe how the transaction log is written to and whether the log truncates after a checkpoint or after a backup. With this combined knowledge we can consider the following backup strategies:

 

Strategy One: Simple

This strategy is suitable under the following conditions:

  1. The database is relatively small
  2. The database does not change minute-by-minute (less volatile)

With this strategy transaction log growth is kept under control, you won’t have to backup the transaction logs but this will mean there may be a small amount of data loss.

How do you do it ?

  1. Set the recovery model to simple
  2. Take full backups on a schedule of your choice (every night perhaps)
  3. If there is a failure you will have to restore the most recent full database backup. That’s it.

Simple recovery means no transaction logs to use in the restore process. You won’t be able to return to a point-in-time and data loss will probably occur. But this of course depends on how dynamic the database is.

 

simple backup

Strategy Two: The Database Only Backup Strategy

This strategy is suitable under the following conditions:

  1. Low transaction volume
  2. The transaction log be on a separate hard disk from database. Hardware failure of the database does not affect the log.

With this strategy, the transaction log is truncated because of a full database backup.

How do you do it?

  1. Set the recovery model to Full or Bulk-Logged.
  2. Take full database backups on your preferred schedule (perhaps every night)
  3. If there is a database disk failure, begin by backing up the orphaned log
  4. Restore from the most recent full backup followed by a restore of the orphaned log.

As you can see, database only backup can be restored to a point-in-time where the that time starts from the last full backup to time of disaster. The orphaned log would have transactions from the last full backup to time of disaster. As long as the log stays safe on another disk from the database your OK. If you lose the log though you lose transactions from the last full backup to time of disaster. As long as you make regular full backups and you have low transaction volume you should be OK.

 

Strategy Three: The Transaction Log Backup Strategy

This strategy is suitable under the following conditions:

  1. Higher transaction volumes (causing increased log growth)
  2. Longer restore time is acceptable

Instead of backing up the database file as a way of truncating the log, you backup the transaction log file. Backing up the transaction log will truncate log and keep its size under control. Although the backup up time will be relatively quick to do, the restore process will take time.

How do you do it?

  1. Set the recovery model to Full or Bulk-Logged.
  2. Take a full database backup that will act as the transaction log baseline (perhaps at 1:00AM)
  3. Take regularly scheduled full database backups with periodic log backups in between (perhaps every at 6 hour intervals; 7:00 AM, 1:00 PM, 7:00 PM)
  4. If there is a database disk failure, begin by taking a backup of the orphaned log immediately
  5. Restore the most recent full database backup, followed by each of the subsequent log backups in the order that they were taken.
  6. Finally, restore the orphaned log.

 

So if your first full backup was taken on Monday at 1:00 AM and a disk failure occurred at 6:00 PM on Tuesday you would take the following steps:

  1. Immediately take a backup of the orphaned log file
  2. Restore in the following order:
    • Full backup from Tuesday 1:00AM
    • T-log backup from Tuesday 7:00 AM
    • T-log backup from Tuesday 1:00 PM
    • Orphaned log at Tuesday 6:00 PM
  3. Pray

 

Strategy Four: The Differential Backup Strategy

The transaction log strategy described above can be slow. The more you have the longer it will take to restore the database to the point of failure. If the changes made to a database are restricted to a particular number or subset of data pages, you could take differential backups instead of full backups. The transaction logs would then need to be restored only from the point of the latest differential backup.

  1. Set the recovery model to Full or Bulk-Logged.
  2. Take a full database backup that will act as the transaction log baseline.
  3. Take periodic full database backups as needed (perhaps once once a week)
  4. Take differential backups between the full database backups to record only the data pages that have been modified since the last full database backup
  5. Take transaction log backups between the differential backups to record the individual transactions between each of the differentials.
  6. If there is a database disk failure, begin by taking a backup of the orphaned log.
  7. Restore the most recent full database backup followed by the most recent differential backup.
  8. Restore all transaction log backups taken since the last differential backup in the order that the backups were taken.
  9. Finally, restore the orphaned log.

sql3

Assuming the above model, a disaster at 1:00 Wednesday would require the following steps:

  1. Immediately take a backup of the orphaned log file
  2. Restore in the following order:
    • Full backup from Monday 1:00 AM
    • Differential from Tuesday 6:00 PM
    • Transaction log from Wednesday 10:00 AM
    • Orphaned log at Wednesday 1:00 AM
  3. Beer

No comments:

Post a Comment