Database Backup Recovery Models - Simple Vs Full

Visit Website View Our Posts

Quite often we are faced with the question of database backups and what are our options, who should be doing the backups, where should we store them, how often should we backup. All of these are great questions that should be asked and addressed.


The first order of business will be to choose the recovery model that best suits your needs and computer hardware. You'll want to identify what your expectations are for recovery and how much storage space will that require. I think the question you'll want to ask yourself and your team is, what are the acceptable losses? No one wants to lose data, but with the right recovery plan, you can minimize your losses and keep your team happy.


Recovery models include backup options like Simple and Full. With a Simple Recovery Model, you can only restore to your last full backup, possibly losing hours of work. For low usage databases, this is typically fine to lose a couple hours of a single person's work. The Full Recovery Model, often referred to as 'Point in time Recovery' will do just that, if you need to pick a specific point in time to roll your database back, you can do this down to the exact moment. This is great for companies running Payroll.


The second consideration in choosing your Recovery Model is where to store all of these backup files. No matter which Recovery Model you choose, backup files will be created to your local hard drive. More files are created when you choose what your acceptable losses are. This is a hardware consideration you'll need take into account, how much hard drive space you have available. The longer you retain these backup files, the more hard drive space you will need. So again, what are acceptable losses, can you afford to roll back one day? Two Days? Three? The more days you feel you'll need, the more hard drive space you'll need.


The third thing you will want to do is move your backups to another location. This step is often skipped or not done completely. The options here would be to use a tape backup system to copy the backup files off the local system hard drive, because what if your hard drive fails (knock on wood). You wouldn't want all of your eggs in one basket, so to speak. Tape backup systems have become antiquated. In those days, you had a tape rotation where someone would manually change tapes and take one home (when they'd remember to do so). These days, we have better options, more automated. Services like this include Azure Cloud Backup. Again, you'll need to consider how much storage space you'll want to purchase to fit your needs. Linked here is the Azure Backup price calculator:


A final thought, check your backups! Too often I find that a backup job, that was working when I last worked with a customer, is now not working for a variety of reasons. Assign someone in your IT or even in the Accounting department to check these backup files. Make sure they are updating daily and that they are being cleared on the proper schedule you've picked. Your backup plan is no good if it's not working!


Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons