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.