SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How do I ... Write a Backup Strategy?

How do I Write a Backup Strategy?

DBA's harp about the need to have backups. The first thing many DBA’s do when they inherit a new SQL Server is to check to see that the databases have backups. I’m not backing off of that assertion now. I’m offering advice on how to determine the best backup strategy for your system.


The best advice for how to develop the optimum backup strategy is to not try to develop a backup strategy. What you should focus on is developing a restore strategy first and then determining what backups are required to support the restore strategy.


Points to Consider

There are 2 key points to consider. In the event of a disaster, how much data are you willing to lose (Recovery Point Objective) and how much downtime can you allow (Recovery Time Objective). First, determine your RPO.


Recovery Point Objective

Recovery Point Objective (RPO) is the point in time in relation to the disaster event to which you need to recover the data. This helps you determine what recovery model is required to support the backups and restores and whether or not log backups are required. And if required, this is when you determine how frequently to back up the log. In the event of a disaster, you may not be able to recover data that has not been backed up, so your log backup frequency should be equal to or more frequent than your acceptable RPO. If your RPO is 15 minutes, then your log backup had better be every 15 or 10 or 5 minutes.


If your application can accept very little or no data loss, this means you must have frequent log backups and be running in full or bulk-logged recovery model. This is a common requirement for OLTP databases.


If your application can rebuild its database from other data sources, then it may be more appropriate to use simple recovery model and only use full database backups. This is common for data warehouses.


Recovery Time Objective

Once you know your RPO, you need to establish your RTO (Recovery Time Objective). This is how long you can be down in order to restore data to the system. This requires understanding the restore sequence and determining which backup scenarios give you the restore time that you require. This helps you determine how often you perform full and differential backups. Practice the restore scenarios using differing mixes of full, differential, and log files to determine what gets you closest to your target RTO.


Be aware that not all target RTO’s are attainable and your RTO will likely need to be adjusted to what is possible. There is some tweaking of the RTO that can be done by using compressed backups, instant file initialization, read-only file groups, multiple data files, and parallel threads with multiple dedicated drives, but that’s a topic for another day.


Now that you know what you need for the restore strategy, write the backup strategy to fit.


Posted by Glenn Berry on 9 April 2010

Good post! Too many novice DBAs do not understand these two basic concepts...

Posted by Robert Davis on 10 April 2010

Thanks Glenn!!

Posted by Dukagjin Maloku on 10 April 2010

Very nice post, thanks for good explanation!

Posted by Jose Chinchilla (sqljoe) on 10 April 2010

Excellent, those 2 key points are going to help me structure my DR/BC documentation I'm working on.

Posted by Christophe Bandini on 10 April 2010

Nice post Robert. I agree totally with Glenn; far too many times these basic concepts seem to be forgotten.

Posted by Robert Davis on 10 April 2010

Thanks everyone!! Not sure how the font ended up so small, but I've fixed that. I hope it's easier to read now.

Posted by Anonymous on 11 April 2010

Pingback from  Do You Have a Career Mentor? | Flexible Working Review

Posted by Jason Brimhall on 11 April 2010

Nice post.  This is good information for novice dbas to learn and seasoned dbas to review.

Can you go back to a small font?  Big fonts hurt too ;) Joking

Posted by Danielle Sullivan on 13 April 2010

Thanks for the great advice!!!

Leave a Comment

Please register or log in to leave a comment.