Today I want to more fully unpack the Database Maintenance Task Wizard features for Incremental Backups and Transaction Log Backups. We looked at them briefly yesterday without much clarification about their intended purpose.
As I stated yesterday, a complete backup is the core of any disaster recovery strategy using backup and restore methodology. The goal is to be able to finish a backup process with the least impact to database availability, while having the quickest time should a restoration process be required. So, any restore process begins by restoring a complete backup. After that there are a number of strategies you may employ.
An incremental backup is taken after a complete backup is made. It tracks ALL of the changes made to the database since the last complete backup was taken. Your restore process may be as simple as restoring the last complete backup, and then restoring each incremental backup that follows. You could do a complete backup on Sunday, and then have daily incremental backups every other day of the week. To restore using this strategy, you would restore the Sunday complete backup, and then restore each daily incremental backup that occurred after the Sunday backup.
Transaction Log Backups are different. As each change is made to your database, prior to the change being performed the intention is first saved to the database transaction log. The transaction log can be backed up after a complete database backup, and be used to restore a database after a failure occurs. Usually, a transaction log backup is taken on a regular basis, with the time span between backups being no longer than the amount of data you are willing to lose. If you are willing to lose the last five minutes data, then you would have a transaction log backup every five minutes.
A transaction log backup differs from an incremental backup in that the transaction log backup is a backup of the transactions, while an incremental backup is a backup of the database data. When you restore an incremental backup it applies the changes to the database directly. When you apply a transaction log backup, it converts the transactions into database changes, and then applies them to the database, just as if it had received the data into the transaction log from real time processes.
You can have a composite recovery strategy using all three kinds of backups. You can restore the complete backup first, followed by all available incremental backups, followed by all transaction log backups that occurred after the last incremental backup. This can save you a lot of time during restoration if you take transaction log backups every 5 minutes. Each transaction log backup must be restored sequentially after the last complete backup or incremental backup. So, an incremental backup may contain the same data as a whole day’s worth of transactional backups taken every five minutes, or 12 x 24 transaction log backups. You can see that it would be a lot easier to restore one incremental backup than 288 transaction log backups.
You don’t have to use incremental backups. You can simply apply transaction log backups after performing a restoration of the last complete backup. The choice is up to you.
When you use the database maintenance task wizard, you may want to create different schedules and types of backups for different databases. A small database might simply be backed up every hour, especially if it doesn’t have much activity. A large database might be backed up every night, have incremental backups every hour, and have transaction log backups every 5 minutes. All of the settings are something you will determine based on your acceptable loss, or non-loss as the case may be.
This should give you enough information to get started on collection your database backups. Don’t forget that a backup method should not be trusted until you have successfully tested a restoration from your backups, and confirmed that the re-created database works for your business.
Cheers,
Ben