Shortly after creating a database on your server you should be planning and scheduling your backup. Easy as it sounds it’s often approached incorrectly. Often the decision when to backup is based on when the server resource usage is low.
Database Backup maintenance requires a design, the design is based on how much data you are willing to lose. Backups are all about the rescue, consider them an ambulance and the patients they carry is your data. When there is a life threating emergency the ambulance has to be close. When we design a backup plan we are creating a schedule for that ambulance to be where you need it, when you need it.
When backing up your data, step one is to determine how much data you can lose, from there we can recommend a backup plan that will meet the business requirements and minimize the risk of accidental data loss.
To ask the question more directly, in a worst case scenario how much data can you lose?
The answer needs to be based on an amount of time, for example: If you have a database that contains HR information and employee information for a small company and can be reproduced then the amount of data that can be lost might be 8 hours. If the company is much larger and the data can’t be reproduced then maybe the time is closer to an hour. Knowing this information gives us the information we need to design the backup solution. For the rest of this post we will assume that you cannot lose more than one hour of data.
SQL Server offers 3 kinds of backups that you should be aware of:
Full Backups – A full backup is exactly how it sounds (with some exceptions). A full backup, will take a backup of everything that is internal to the database; data, tables, code, and users with permissions inside the database. That exception I was talking about is the logins. In many cases the login to access the database is backed up with the master database, not the user database. In addition jobs that execute are backed up with the MSDB database and not the user database.
Differential Backups – Otherwise known as a differential backup is just how it sounds. The data contained in this backup includes the pages that have been changed since the last full backup (pages that are different). There are a couple of specific items that I should make sure are pointed out. First, this is a backup of the page that has changed, at the time of the diff backup. The point I am trying to call attention to is that if the data on that page has changed multiple times, each multiple is not stored in the diff backup, only the state of the page when the diff is taken. The second point I am want to call out is; the diff backup is a backup of all the pages from the last full Backup. This means when you are going to restore the database using a diff backup you only need the most recent full backup and then the most recent diff backup.
Transaction Log Backups – Transaction log backups are descriptive as well. These are a backup of all the transactions that are stored in the transaction log. What is in the log? The transaction log contains each insert, update and delete statement. Again there are two points that I would like to call your attention to. The first, transaction logs keep each transaction, this is so we can restore the database to a specific point in time. The second is that if you were to restore the database using the transaction logs, you are going to need each transaction log taken from the most recent diff or the most recent full.
On a Side Note:
One of the first clients I had as a consultant had a restorability concern. When we talked about his database he was under the impression that he should be able to restore his database to within an hour of downtime. After review of his server, I found that after the original install was completed a transaction log backup job was created. For the next 4 years he didn’t run another full. We quickly changed his backup plan with I let him know we were going to have to restore the most recent full and all the transaction logs after. This meant that after four years we were going to need to find over 35,000 files to have a successful restore.
There are other backup options. Once you understand the backup options that you have and how the individual options work, you are ready to look at how much data you can lose. If you can only lose an hour of data, you may find that running a full or a diff backup every hour to be cumbersome. But an option could be to run a full every night and then a transaction log every hour. There is a lot of flexibility to how you can backup your database so when you need to restore it, you have the data you need.