In a previous tip we discussed the different recovery models SQL Server offers and decision points on which to base your recovery model selection. After you've selected the appropriate recovery model, you need to put in place the proper backup strategy to minimize data loss and downtime in case of a database failure.
I have seen many database installations wherein the correct recovery model is in place to minimize data loss, but the correct backup options and plans are not in place. Often the Full Recovery model is selected, but the only backups occurring are full backups. Occasionally, the Simple Recovery model is selected, even though the customer base could not afford to lose all transactions since the last full backup.
Creating a full backup once a day probably makes a lot of sense to most people, but that should only be the beginning of your backup strategy. We will take a look at some of the components to help define a backup strategy as well as the different backup options that are available.
First, let's look at the backup options:
Database (Full)
Transaction
Differential
Others
Unfortunately, SQL Server does not have an option for simply setting up a backup model like it does for the recovery model. It would be nice if you could just select a backup model from a drop-down list. The closest thing to that is Maintenance Plans, but the options in Maintenance Plans do not give you the choice of doing Differential backups, and you still need to set the proper backup schedule.
Based on t
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
he recovery model you have selected, you now have to choose which type of backups to run -- but more importantly, the time and frequency of the backups.
So, what is the best backup model to use for your database?
If you are using the Simple Recovery model:
If you are using the Full or Bulk-Logged Recovery model:
How to implement your backup model
You can run backups manually, but the best approach is to schedule backups using SQL Agent. Once you set up the backup job, let SQL Agent run the backups on a set schedule. This can be done one of three ways:
[IMAGE]
[IMAGE]
Summary
The types of backups and the schedule you use will allow you to control how much data could be lost in case of a failure as well as the time it takes to do a recovery. Using Differential backups could dramatically decrease the time it takes to do a restore of your database and get your users up and running quicker. Using Maintenance Plans is also a place to start, but take the time to get familiar with all of the backup options and then select a plan that matches your business goals.
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Greg, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
