 |
|
Selecting the SQL Server database recovery model to ensure proper backups
|
By: Greg Robidoux | Read Comments (13) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More |
|
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.
T-SQL
| -- set to Full recovery ALTER DATABASE AdventureWorks SET RECOVERY FULL GO -- set to Bulk Logged recovery -- set to Simple recovery |
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
Last Update: 5/13/2008
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Friday, May 30, 2008 - 3:10:24 AM - VijayA | Read The Tip |
|
I think, just taking a differential backup is sufficient, after changing the recovery model from Simple to Full. |
|
| Monday, June 02, 2008 - 11:19:51 AM - tosscrosby | Read The Tip |
|
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. |
|
| Sunday, June 22, 2008 - 7:47:26 AM - lohrey2 | Read The Tip |
|
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. |
|
| Sunday, June 22, 2008 - 6:05:41 PM - aprato | Read The Tip |
|
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.
|
|
| Monday, June 23, 2008 - 6:15:01 AM - tosscrosby | Read The Tip |
|
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? |
|
| Monday, June 23, 2008 - 6:15:15 AM - lohrey2 | Read The Tip |
|
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:30:26 AM - grobido | Read The Tip |
|
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 - 7:15:47 AM - aprato | Read The Tip |
|
To expound on Greg's post, here's a visual example of a FULL strategy I employed in the past where I backed up
Sunday Mon Tues Wed Thurs Fri Complete 7am Log Backup Log Backup Log Backup Log Backup Log Backup 1am Differential Differential Complete DB Differential Differential
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 - 9:08:34 AM - tosscrosby | Read The Tip |
|
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........ |
|
| Saturday, June 28, 2008 - 4:54:59 AM - lohrey2 | Read The Tip |
|
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. :) |
|
| Sunday, January 04, 2009 - 10:55:47 PM - snakyjake | Read The Tip |
|
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 |
|
| Monday, January 05, 2009 - 8:47:04 AM - aprato | Read The Tip |
|
http://msdn.microsoft.com/en-us/library/aa224769.aspx |
|
| Tuesday, February 22, 2011 - 1:20:28 AM - Addision Philip | Read The Tip |
|
I always use differential sql recovery backup to maintain the backup. |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |