Selecting the SQL Server database recovery model to ensure proper backups

By:   |   Comments (19)   |   Related: > Recovery Models


Problem

One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure.  Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly.  In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.

Solution

For SQL Server 2000 and 2005, Microsoft offers three different recovery models for your databases.  Each database on your server can be setup differently and you also have the ability to change the recovery model as needed, so this choice is not permanent.

The three recovery models are:

Simple

The simple recovery model does what it implies, it gives you a simple backup that can be used to replace your entire database in the event of a failure or if you have the need to restore your database to another server.  With this recovery model you have the ability to do complete backups (an entire copy) or differential backups (any changes since the last complete backup).  With this recovery model you are exposed to any failures since the last backup completed.   Here are some reasons why you may choose this recovery model:

  • Your data is not critical and can easily be recreated
  • The database is only used for test or development
  • Data is static and does not change
  • Losing any or all transactions since the last backup is not a problem
  • Data is derived and can easily be recreated

Type of backups you can run:

  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups

Bulk_Logged

The bulk logged recovery sort of does what it implies.  With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log.  The advantage of using this recovery model is that your transaction logs will not get that large if you are doing bulk operations and you have the ability to do point in time recovery as long as your last transaction log backup does not include a bulk operation as mentioned above.  If no bulk operations are run this recovery model works the same as the Full recovery model.  One thing to note is that if you use this recovery model you also need to issue transaction log backups otherwise your database transaction log will continue to grow.  Here are some reasons why you may choose this recovery model:

  • Data is critical, but you do not want to log large bulk operations 
  • Bulk operations are done at different times versus normal processing.
  • You still want to be able to recover to a point in time

Type of backups you can run:

  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups

Full

The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable.  With this model all operations are fully logged which means that you can recover your database to any point.  In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.  Here are some reasons why you may choose this recovery model:

  • Data is critical and data can not be lost.
  • You always need the ability to do a point-in-time recovery.
  • You are using database mirroring

Type of backups you can run:

  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups

Changing Recovery Models

The recovery model can be changed as needed, so if your database is in the Full recovery model and you want to issue some bulk operations that you want to minimally log you can change the recovery model to Bulk_Logged complete your operations and then change your database model again.  The one thing to note is that since there will be a bulk operation in your transaction log backup that you can not do a point in time recovery using this transaction log backup file that contains this bulk operation, but any subsequent transaction log  backup can be used to do a point in time recovery.

Also, if your database is in the Simple recovery model and you change to the Full recovery model you will want to issue a full backup immediately, so you can then begin to also do transaction log backups.  Until you issue a full backup you will not be able to take transaction log backups.

To change the recovery model you can use either SQL Server Management Studio or T-SQL as follows:

Management Studio

  • Right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list and select OK to save.
adventure works

T-SQL

-- set to Full recovery
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO -- set to Bulk Logged recovery
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO 
-- set to Simple recovery
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO
Next Steps
  • Now that you know what recovery models exist, take the time to check your databases and setup the appropriate model and backup plan
  • As mentioned you can change the model as needed, so use this to your advantage if you need to run some large bulk operations such as a bulk insert or a lot of index rebuilds


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, July 26, 2019 - 9:15:36 AM - Welday Teka Back To Top (81875)

Thank you for information great job

Thursday, May 12, 2016 - 11:16:57 AM - Avtar singh Back To Top (41470)

 

 Usefull information. Thank you so much.

 

Thursday, June 26, 2014 - 8:03:18 PM - Arjunbeer Back To Top (32426)

Very good post , I still see Ax (Dynamics) database in my company in simple recovery model. we are still in dev stages.

 

Monday, January 20, 2014 - 10:20:38 AM - Fadhl AL-Sharee Back To Top (28147)

good article.. thanks.

Monday, September 24, 2012 - 5:03:52 PM - hp_dba_uk Back To Top (19662)

Thanks Greg DBCC sqlperf(logspace) - is really useful as I have so many DBs.   Also I read up on this from below:

http://www.sqlservercentral.com/articles/Transaction+Logs/72488/ 

http://technet.microsoft.com/en-us/library/ms189493(v=sql.90).aspx

Aslo help on the SSMS gui shrink page is good too:

Currently allocated space

For data files, displays the current allocated space. For log files, displays the current allocated space computed from the output of DBCC SQLPERF(LOGSPACE).

Available free space

For data files, displays the current available free space computed from the output of DBCC SHOWFILESTATS(fileid). For log files, displays the current available free space computed from the output of DBCC SQLPERF(LOGSPACE).

 

I have alrady started done manual shrinking on few DBs with big tran logs, which has freed up few Gigs..:-)

Thanks

 

 

Monday, September 24, 2012 - 12:05:37 PM - Greg Robidoux Back To Top (19660)

@Patel H - just changing the database recovery model will not free up disk space.  The transaction logs will still use the same amount of space on disk, but the log file itself may not being using as much.

You should run DBCC sqlperf(logspace) to see the percentage of space used in each log file and then you will need to run DBCC SHRINKFILE to shrink each transaction log file to regain disk space.

Monday, September 24, 2012 - 5:17:51 AM - Patel H Back To Top (19650)
Hi there,

I used a script to changed around 60 cust-test DBs from Full to simple recovery model as they were not needed to be in Full recovery mode etc

I did the change on Fri and today is Monday..I was expecting the see lot of space freed up on the drive where files are stored, by this as thinking it will run checkpoint etc....but below reasons tells me it wont happen straight away:
================
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

The log becomes 70 percent full.

The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.
=================


Some of these DBs have never been backed up but were in Full recovery model. So does this change anything?
or is it a simple matter of watining?

Monday, January 5, 2009 - 8:47:04 AM - aprato Back To Top (2502)

 http://msdn.microsoft.com/en-us/library/aa224769.aspx

Sunday, January 4, 2009 - 10:55:47 PM - snakyjake Back To Top (2498)

Great article.  Question...

The article says that when in BULK LOGGED recovery, bulk operations are minimally logged.

1.  Are bulk operations minimally logged in SIMPLE recovery mode?  The same as BULK LOGGED?

2.  In BULK LOGGED, what is "minimally logged"?  This must mean that something is getting logged.  Same question for SIMPLE.

 

Thank you,

Jake


Saturday, June 28, 2008 - 4:54:59 AM - lohrey2 Back To Top (1286)

This may be a few days late in coming but I just wanted to say Thanks for taking the time to answer my questions.  I will hopefully be able to return the favor and help someone else at some point in time, but until then it is nice to know I have somewhere to go with my confusion. :)

Monday, June 23, 2008 - 9:08:34 AM - tosscrosby Back To Top (1230)

I totally agree with aprato. Setup the appropriate backup strategy for your business. Full backups daily and log/differentials at the right intervals. Restoring should be as easy as possible (although when it does become necessary it's usually not painless) so the right sequence is important. I have a full backup each night and logs every thirty minutes, which based on business needs, allows for no more than 30 minutes of lost data. I have another system that is full backups each night and hourly logs (one hour of lost data is acceptable on this system). And as aprato stressed, test your backup files. It doesn't do you any good to say "I have a backup" if you cannot restore the data and know that it works........

Monday, June 23, 2008 - 7:15:47 AM - aprato Back To Top (1229)

To expound on Greg's post, here's a visual example of a FULL strategy I employed in the past where I backed up
the trans log every hour.   This particular company had business hours between 6am and 7pm, Mon-Fri.

 

Sunday                                          Mon            Tues                Wed                  Thurs               Fri               
======                                         ====          ====              ====                   ====            ====             

Complete                      7am       Log Backup   Log Backup     Log Backup         Log Backup   Log Backup     
DB Backup                   8am       Log Backup   Log Backup     Log Backup         Log Backup   Log Backup
                                    9am       Log Backup   Log Backup     Log Backup         Log Backup   Log Backup                                               .
                                     .
                                     .
                                     . 

                                  1am       Differential    Differential       Complete DB        Differential     Differential
                                                   Backup       Backup            Backup               Backup          Backup

 

I can't stress enough that you should perform frequent fire drills to make sure that your backup strategy is working... always be prepared for the worst.

Monday, June 23, 2008 - 6:30:26 AM - grobido Back To Top (1224)

Here is some clarification.

You have to always start with a FULL backup.  Once you have done at least one of full backup you really do not need to ever do a full backup again, but this is not a good practice.

Once you have completed a FULL backup you can then do either DIFFERENTIAL or TRANSACTION LOG backups.  Again transaction log backups can only be issued if the database is in FULL or BULK-LOGGED recovery.

The key here is the restore process.  To minimize the number of files to restore you can do the following. 

Restore the FULL backup in no recovery mode, restore the latest DIFFERENTIAL backup in no recovery mode and then restore all of your TRANSACTION LOG backups that were taken after the last DIFFERENTIAL.

So if you are issuing both differential and transaction log backups it is the restore process that is really going to get the benefit.  The differential backups really just help by cutting down the number of restores you need to do.

Another way you could restore your database even if you are using differential backups is to restore the FULL backup and all of the transaction log backups that have occured after the FULL backup.

Monday, June 23, 2008 - 6:15:15 AM - lohrey2 Back To Top (1223)

Hi,

Okay, that makes sense.  The recovery model you use as your default depends on the type of database you are working with. 

As to the rest of your statement, if you are performing a trans log backup every hour then you must also be performing a FULL along with it.  If I am understanding this correctly, before you can perform a differential or trans log backup, you must first perform a FULL.  If you had a situation like this: FULL taken at midnight, differentials taken every three hours during the day, you would only be able to execute a trans log backup once per day (after the FULL at midnight).  Do you not need to perform as many trans log backups if you are executing differentials during the day?

Monday, June 23, 2008 - 6:15:01 AM - tosscrosby Back To Top (1222)

What is an acceptable amount of data loss for the user? What does your SLA state? I have some databases that I do a full backup each night and logs every 30 minutes. Other databases, I have a full each night ant differentials every 4 hours. The key point is, what is an acceptable amount of data loss?

Sunday, June 22, 2008 - 6:05:41 PM - aprato Back To Top (1216)

SIMPLE is good for development and QA databases - there's less work on the DBA's part because the db engine will automatically truncate the transaction log based on database CHECKPOINTs.

If you have a production database, then you may want to consider FULL with log backups taken at periodic intervals. The amount of data loss that is acceptable is taken into consideration. At one site, I took them once an hour. At a another, I took them every 30 minutes.  In addition, you'll have to monitor disk space as the log grows.  If you don't take periodic trans log backups, the trans log will grow.

 

Sunday, June 22, 2008 - 7:47:26 AM - lohrey2 Back To Top (1215)

 

Hi,

As you will most likely be able to tell, I am a complete newbie at this...BUT I am a little confused about when you would even want to make the choice to use the SIMPLE Recovery model.  If FULL is the default and the recommended choice, and you would have to actually physically make the change to SIMPLE (and then back again), why not just leave it at FULL?  The logic escapes me.

Monday, June 2, 2008 - 11:19:51 AM - tosscrosby Back To Top (1066)

It all depends on your business needs (what is the amount of data loss your users will accept??) or SLA.I have some databases that the users will allow for one full day of data loss. Another environment will allow one hour of loss and yet another environment will tolerate 30 minutes of data loss. I perform full backups on weekends, differentials each night and log backups throughout the day. A nice mix that works for my environment.

 

Edit - If you are in full recovery mode, log backups are essential.

Friday, May 30, 2008 - 3:10:24 AM - VijayA Back To Top (1061)

I think, just taking a differential backup is sufficient, after changing the recovery model from Simple to Full.














get free sql tips
agree to terms