Recovery Models in SQL Server 2005


Database backup is one of the most important job which a database administrator needs to perform everyday. Since the data within the database is critical and should be available any time when a request is raised for it. Many times, it has been said that the important and the most critical data should be backed up on a regular basis. However, my personal opinion differs from it a bit. I believe that every data a human / business / System / Company ever need, is critical information and should be handle with care.

In terms of SQL Server databases, the data availability is depended on the Recovery model of the database. Recovery Model can be defined as the amount of data made available for the end-users in case of a disaster. Many authors defined it in their own way, and what I understood of the fact, consists of the above line. In case of SQL Server 2005, there are three types of recovery models available. They are:

  • Simple Recovery Model
  • Bulk-Logged Recovery Model
  • Full Recovery Model

Let’s have a look at these three types of Recovery Models.

Simple Recovery Model

As the name suggest, Simple recovery model requires less administrative effort as compared to the other two types of recovery models. Simple recovery model does not support the transaction log backup causing the recovery of the database left till last available data file backup. Since the T-log (transaction log) backup is not possible, we can not recover the recent data. When a database is using Simple recovery model, the transaction log of the database has been truncated after the release of the checkpoint. The space acquired by the t-log file will be released back to the operating system after the checkpoint operation completed.

Simple Recovery model mostly used in development and test environment, where the data is not critical and can be easily recreated. This model can also be implemented for application which used historical / read-only data.

Bulk-Logged Recovery Model

Both data and transaction log backup is possible, if the database is using Bulk-logged Recovery model; however the bulk operation of data is not recorded in the Transaction log. This model uses less transaction log space by bulk logging most of the bulk operations. Since transaction log backup is possible using this recovery model, the data can be recovered by using both the data and log backup till the bulk operations. The bulk operations are completed quickly using this recovery model as the operations does not have to write the details of every transaction in the transaction log file.

Most of the time, this recovery model is used in conjunction with Full recovery model. Using this model, we can recover more data as compared to Simple Recovery model. Point-in-time restore is also possible using this model, however, we should also remember that the point-in-time restore failed if we are trying to restore to a point when a bulk operation was going on.

Full Recovery Model

Full Recovery Model supports both data and log backup. Using Full recovery model, we can recover data till the time of the disaster. Since the Full recovery model records each transaction in the transaction log, we can recover the database till the point of failure, if the tail backup is performed. Since it records all the transactions in the log, the size of the transaction log will increase causing a slowness during bulk operation. When a database server failed due to any reason, a t-log backup is initiated after the server boot-up next time. This t-log backup is also known as tail backup. When a database is restored from the available data and log backups along with the tail backup, we will be able to recover the data till the time of failure.

Most of the time, in Production environment, we set the recovery model of the databases as Full while we still switch to Bulk-logged recovery model before a bulk upload operation. Full recovery model also support restoration of the data pages in a database.

Note: Both the backup and restore operations depend on the above recovery models of the databases. Please look at the below tables to get an early info about the backup and restore operation. In our next article, we will dive into the backup process.

Recovery Model Full Data Backup Differential Data Backup Transaction Log Backup
SIMPLE YES YES NO
Bulk-LOGGED YES YES YES
FULL YES YES YES
Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s