Recently, I’ve had some questions about best practices and tips and tricks for SQL Backups. Questions ranging from how often should backups be run and how long they should be kept for to the right types of backups to do on a regular basis. The simple answer to these questions is “whatever works best in your environment”. Let’s examine some scenarios to help you understand which approach is best for you.
The first example we’re going to look at is utilizing Full Recovery in your database environment. When your database is set to full recovery, you are enabling your company to experience less data loss in the event of a catastrophic failure. Full recovery is ideal for organizations that cannot afford to lose more than an hour or two of data entry. This is because multiple, smaller backups are run throughout the regular business day. Here’s how it works:
Whenever a transaction is entered into CRM or GP, there is a record of the transaction stored in the transaction log database. In full recovery mode, these transactions remain in the transaction log database until they are removed via Transaction Log Backups. These backups truncate (delete) the transaction log and store the transactions that have already been committed to the live database. In the event of a catastrophic failure, this enables the users to restore the last full backup taken, plus restore the transaction logs of the uncommitted transactions, and bring the database back to the point of the last transaction log backup.
If your database is in full recovery mode and you do not have transaction log backups running, your transaction log database is probably getting huge because those uncommitted transactions are not getting truncated from the database. If this is the case, you’ll want to run a transaction log backup first, and then manually shrink the size of your transaction log database (you’ll notice that the used space drops dramatically after this backup, but the free space remains high).
The other type of recovery mode you can run in your SQL environment is Simple Recovery. In simple recovery mode, transactions are truncated from the transaction log database once they are committed. Transactions are committed once the database hits what is called a Checkpoint. Once this checkpoint occurs, transactions are committed to the database, and the transaction log is truncated. This ensures that the size of the transaction log database does not get out of hand, and doesn’t require having transaction log backups running throughout the day.
The downfall to simple recovery for organizations who cannot afford a great amount of data loss is that there is not the ability to run transaction log backups throughout the day. If you need backups to run throughout the day, you’ll need to run full backups when the system resources are not being utilized (like over lunch or coffee breaks). Typically, companies running in simple recovery mode run nightly backups, and if something were to happen during the day, they would reenter the transactions up to the last full backup.
The advantage to having a database in simple recovery mode is the ease of use in setting up and monitoring the maintenance plans. You only need to run full backups nightly (or as frequently as you would like). There is no need to run transaction log backups to truncate the transaction log database as these are truncated at the checkpoints throughout the day.
Hopefully this will help you decide which SQL Maintenance plan to setup for your organization. If you need any help determining an appropriate plan for your particular organization, please feel free to contact me and we will create a plan that works best for your organization.