Best Practices for Configuring SQL Backups
How much data can you lose? How long can you go without your data? The answers will help you develop a sound backup strategy.
- By Eric Johnson
- 10/24/2007
Last time, we discussed the different backup types available in SQL Server. Now, let's take a quick look at some best practices when it comes to configuring those backups.
Although there is no one set of best practices for database backups, there are indeed best practices for determine how you should back up a database given the environment and circumstances. You will need to ask yourself two very important questions in order to select the best mix of backup types:
- How much data can you afford to lose?
- How long do you have to recovery if a failure happens?
When considering the first question, think in terms of time, not size. In other words, can you afford to lose a few hours of data or is a loss of only a few minutes too much? This decision is largely driven by the business. For example, losing even a single credit card transaction could cost your company money, but losing 300 Web site sessions may be fairly harmless.
If you need to back up more frequently to avoid data loss, you will want lots of t-log backups. Since most t-log backups are small when they are run frequently, backing them up every 30 or even every five minutes is not out of the realm of reason.
This then leaves you with the decision as when to perform your full backup. I often do this daily for smaller databases, where the backup takes an hour or less, and weekly for larger databases, where the full backup can take upwards of five hours. If I am backing up weekly, I will usually do a differential backup once a day to lessen my recovery time.
Recovery time is the other factor that will drive your backup strategy. If you need to recover quickly, you will want to minimize the time between your full backups or add differential backups to the mix.
If you only do a full backup of your database once a week, say on Sunday, and then do t-log backups every 15 minutes, a failure on Friday afternoon could leave you restoring your full backup followed by more than 500 t-log backups. To reduce this overhead, you could perform a differential backup once a day and continue with your t-logs at 15 minute intervals. With this set up, at a worse case you would be looking at restoring one full and one differential followed about 90 t-logs. If that’s still too much, maybe you should think about doing differential backups at noon and midnight each day to further reduce the number of t-logs you will need to restore.
SQL Server Help —
Just An E-Mail Away |
Need SQL Server troubleshooting help? Or maybe you want a better
explanation than provided in the manuals? Describe
your dilemma in an e-mail to the MCPmag.com editors
at [email protected];
the best questions get answered in this column and garner
the questioner with a nifty Redmond T-shirt.
When you send your questions, please include your
full first and last name and location with your message. (If you prefer to remain anonymous,
specify this in your message, but submit the requested
information for verification purposes.) |
|
|
Since each environment is different, there is no one correct answer for everyone. However, if you base your backups on how much data you can lose and how long you can afford to be down during your restore, you will be well on your way to setting up sound backup strategies.
Next time, making the right decisions with tape backups.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.