Developing a SQL Server Database Backup Plan

One of the most important parts of the DBAs job is to ensure that proper backups are being taken at the required interval. The last thing that anyone wants to have happen is to be the next JournalSpace.com and having the company have to close because there wasn’t a usable backup of the database.

The reason for backups is hopefully obvious to all. They are used to recover the database to a usable point in time in the event of a total hardware failure or a user mistake that deletes some or all of the data from the database. Without the ability to restore the database if one of these events were to happen the database would be lost without any ability to get the information back.

There are three basic kinds of backups that you have to select from within SQL Server. These are the Full Backup, Differential Backup, and the Transaction Log backup. Each of these backups has its own purpose. The types of backups that you take and how often will be dictated by the business units that you support as well as the type of database that you are backing up.

Data Warehouses

Data warehouses are usually the easiest kind of database to backup. This is because in a typical data warehouse all the data changes are made at once, usually in the morning. Because of this, the data warehouse will typically be running with the simple recovery model as keeping the transaction log is not a requirement. The most typical backup configuration for a data warehouse is to perform a full backup weekly with differential backups performed daily.

Depending on the amount of data that is being changed each day, after two to three days it may become more cost effective to perform a full backup then continue with the differential backups again. This would give you full backups on Sunday morning and Wednesday morning with differential backups the other days.

Most data warehouse administrators will schedule the backups to run just after the database loading has been completed. This way they can always restore the system back to the state it was after the load had completed.

OLTP Databases

OLTP databases are nowhere near as simple as data warehouses to backup. In an OLTP database the data is constantly changing and you will probably be required to restore the database to a point in time in order to recover from a user deleting or changing data in addition to being able to quickly recover the database in the event of a hardware failure.

All too often contractors and websites will recommend doing a full backup weekly, a differential daily, and transaction logs every 15 minutes. And this is a fine setup for some databases; however it may not meet the requirements that are set by the business unit. All too often IT dictates to the business unit what the recovery interval will be, instead of asking the business unit what the recovery interval should be. Since the business is the one that needs the data they should be consulted as to how much data loss is acceptable.

If the business comes back to you saying that losing data back to that morning is acceptable then performing database backups every 15 minutes is overkill and you are performing un-needed backups. In this case a weekly full, with daily or twice a day differential backups would be perfect.

However if you have setup twice a day backups, and the business comes to you saying that they can only afford too lose 5 minutes of data, your backup plan isn’t going to be good enough. In order to setup a backup plan to ensure less than 5 minutes of data loss, you would perform full backups daily, differential backups every few hours and transaction log backups every 5 minutes.

When performing frequent transaction log backups, differential backups are recommended so that you can reduce your time to restore.

Keep Restore Time in Mind

When deciding on your database backup plan you should also keep in mind your database restore time. Performing a weekly full backup with transaction log backups every 5 minutes may technically meet the backup requirements, but restoring those transaction logs will take a long time. It will take much longer than it will take to restore a differential backup and just a few dozen transaction log backups once the differential is complete.

In fact the acceptable restore time is another question you should be asking the business unit. If it is acceptable that restoring the database takes longer to restore then perhaps not taking the differential backups is the correct course of action. However if the restore time must be as short as possible then differential backups should be included in your backup plan.

Conclusion

One thing to remember about differential backups is that they require more CPU power to create than full or transaction log backups. This is because SQL Server needs to identify which rows and objects have been updated since the last time the full backup was taken. As more and more of your data is changed, the differential will take more time and CPU power to create. If your database server’s CPU is already very high during the peak time, adding the differential backup may add an unacceptable amount of load to the SQL Server.

The key thing to remember about setting up your backup and recovery plan is that so single plan fits every database in your environment; and since every environment has different databases, each environment should have a different database backup and recovery plan. Because of this you shouldn’t take a cookie cutter approach to your database backup setup. Consult with the business units that use the data and take their feedback into account when making your backup plan.