Database Recovery Models in SQL Server

One of the primary responsibilities of Database Administrators is to make sure that the databases are backed up at regular intervals so that in the case of a disaster the DBA can bring the SQL Server database back online for the business to continue without much downtime. We have already discussed in detail the Importance of Database Backups and Recovery Plan in my previous article. In SQL Server 2000, 2005 and 2008 Microsoft offers us three different recovery models for user database. It is very important to choose the appropriate recovery model for the user database based on the business requirement along with a proper database backup plan. Different Types of SQL Server Database Recovery Models
From SQL Server 2000 onwards Microsoft supports three different types of recovery models. They are Simple Recovery Model, Bulk Logged Recovery Model and Full Recovery Model. It is possible to have different recovery models configured for different database on a single instance of SQL Server. Once the recovery models is selected for a particular database, based on business requirements database administrator can switch the recovery model for the particular database. Let us take a look at each of the SQL Server Database Recovery Models in detail. Simple Database Recovery Model
In Simple Recovery Model SQL Server logs minimal amount of information in the transaction log. SQL Server basically truncates the transaction log whenever the transaction log becomes 70 percent full or the active portion of the transaction log exceeds the size that SQL Server could recover in the amount of time which is specified in the Recovery Interval server level configuration. If the user database is in Simple Recovery Model then you will not be able to perform the transactional log backup as it is not supported. This also means that in case of any disaster you won’t be able to take the transactional log backup and thereby point in time recovery is not possible. In this approach you will end up losing data due to the non availability of transaction log backups. The database can only be restored to the last backed up Full or Differential database backup. Therefore, under Simple recovery model the database needs to be backed up at shorter time intervals to avoid as much loss of data. However, the backup time intervals should be long enough to avoid the backup overhead on the database. Ideally Simple Database Recovery Model is best suited for user databases which are in Development or Test environment; where the need of frequent database backups is very less. This recovery model is also suited for databases which mostly contain read only data or on those databases where changes happen less frequently. The Simple database recovery model is not suited for production system where lot of user activities happen very frequently. For Production databases Full Recovery Model should be used. Bulk Logged Database Recovery Model
In Bulk Logged Recovery Model SQL Server logs most of the bulk operations in the transaction log. When the user database is in Bulk Logged recovery model the bulk operations such as CREATE INDEX, SELECT INTO, BULK INSERT, BCP etc are minimally logged within the transaction log thereby improving performance. As the bulk log operations are minimally logged it will reduce the log space consumption. However when the database is in this recovery model, the point in time recovery is not possible as long as the last transaction log backup has any of the bulk logged operations. If the transaction log does not have any bulk log operation then point in time recovery is possible. If there are no bulk operations running then this recovery model will work similar to that of Full recovery model. It will be good to switch the database recovery model from Full to Bulk Logged when you are running the bulk operations, this will help you save disk space for the transaction log and it will also improve the database performance during bulk operation. One of the important things to be noted is that the transaction log needs to be backed up regularly to prevent not only the loss of work but also to reduce the transaction log size. In this recovery model the transaction will continue to grow until the transaction log is backed up. Ideally Bulk Logged database recovery model is best suited for those user databases where bulk operations are performed nightly or at times when there are no users using the database. This recovery model can also be used to reduce the transaction log from becoming very big during the bulk operations. Full Database Recovery Model
If you are using Full Recovery Model the point in time recovery for the database is possible as long as your database backups are intact and the transaction log tail backup is available. This recovery model is the best suited for production database where you cannot afford to lose your critical data. All the bulk operations such as CREATE INDEX, SELECT INTO, BULK INSERT etc are fully logged and are recoverable. In this recovery model the transaction log basically logs all the transactions and it is retained until you backup the transaction logs. When the database is in a Full recovery model the database backup plan should be configured to use the combination of Full, Differential and Transaction log backups. By default when a user creates a new database, it is created with Full recovery model as MODEL system database is running under Full recovery model. One important thing to be noted is that some of the features of database mirroring will not work when the database is not in full recovery model.