For a long time our only backup strategy was to take a full backup of the database every night and try not to worry about it too much. Eventually the database grew to a point where the job that copied the backups to off-site storage couldn't keep up any more, not to mention that all that copying was consuming some serious bandwidth. Then some bright spark realized we couldn't really afford to lose a whole day of work if the server ever crashed and we had to restore the last backup. We obviously needed a better plan.
While any good DBA will tell you there are a million things to consider when deciding on your backup strategy, we needed a plan that was simple enough for a developer to implement and maintain. It also had to create small(er) backups and provide better coverage. Too complicated? Well, we came up with a pretty good solution.
To improve our coverage, we could take more frequent transaction log backups since they are small and fast even with a reasonably large database. The only problem is that log backups have to be restored sequentially on top of the correct full backup. If one of those files is corrupt, all the data from that file onwards is lost. So, we didn't want too many log backups.
On the other hand, we had to reduce the frequency of the full backups to ease the load on the bandwidth. That's when we turned to differential backups to fill the gap. Differential backups are cumulative i.e., they include any changes that occurred since the last full backup. To restore, you only need to apply the last differential on top of the correct full backup. The downside is that, over time, the differential backup will grow to the same size as the full.
This lead to our three-pronged approach. We decided to have:
While any good DBA will tell you there are a million things to consider when deciding on your backup strategy, we needed a plan that was simple enough for a developer to implement and maintain. It also had to create small(er) backups and provide better coverage. Too complicated? Well, we came up with a pretty good solution.
To improve our coverage, we could take more frequent transaction log backups since they are small and fast even with a reasonably large database. The only problem is that log backups have to be restored sequentially on top of the correct full backup. If one of those files is corrupt, all the data from that file onwards is lost. So, we didn't want too many log backups.
On the other hand, we had to reduce the frequency of the full backups to ease the load on the bandwidth. That's when we turned to differential backups to fill the gap. Differential backups are cumulative i.e., they include any changes that occurred since the last full backup. To restore, you only need to apply the last differential on top of the correct full backup. The downside is that, over time, the differential backup will grow to the same size as the full.
This lead to our three-pronged approach. We decided to have:
- full backups once a week
- differential backups every 4 hours
- transaction log backups every 15 minutes
No comments:
Post a Comment