Last time we were talking about how the maintenance wizard can help you with syntax to get maintenance jobs created and working. However, using the maintenance wizard cannot replace the knowledge you need to have to determine what maintenance jobs need to be done, and when they should be performed. Today I’m going to talk at a high level about database backups and recovery models.
Out of the three kinds of backups, full, differential and transaction log, you schedule different backup tasks to be performed. When and what backups you schedule depends on how much time you need to bring a new database back online from your backups, and how much data loss you are able to allow. So, it is the restoration process that determines what backups you create, and when they are scheduled to run.
A full backup makes a complete copy of your database. You can restore a full backup, and start using your database after the restoration is completed. Generally, a full backup can take a while to execute, and if the database is of any reasonable size, could be of substantial size. Often times we simply schedule a full backup once daily. If a database is small enough you could easily do a full backup more frequently.
The other backup methods work in conjunction with a full backup. When you create a differential backup, it backs up all data from the current state that has been modified since the last full backup. From a restoration perspective, if you performed a differential backup every hour after a full backup was executed, your disaster recovery would simply be to restore the last full backup, and then restore the last differential backup, since it contains all data modifications. You can simply ignore all the other differential backups that occurred earlier. This model allows you to quickly restore your database by only applying the two files needed to get to your desired point in time.
The last type of backup, the transaction log backup, works differently from the differential backup. Transaction log backup are cumulative since the last full backup. It’s easiest to understand what that means by how they are used to restore a database. Transaction log backups simply make a copy of all the transactions in the database transaction log that have been completed, and purge them from the log. This has two benefits. It truncates the log so the space can be used for new transactions. The second is that it keeps a history of transactions.
So, to restore a database from transaction log backups you first restore the database from a full backup. You can, if you have them, restore from a differential backup. Finally, you restore each transaction log following the full backup or differential backup, in the order they were created from the database.
If you setup a backup schedule to do a full backup at midnight, a differential backup every hour, and transaction log backups every minute, you could restore your database to the state of any minute within that 24 hour period, and never have to apply more than 60 backup files, worst case. To restore your database to 10:59 you would restore from the midnight backup. Then you would apply the 10:00 differential backup. Then you would restore from each transaction log starting at 10:01 through 10:59. If you just wanted to restore to 10:00 you could simply restore from the full backup and the differential backup, ignoring all the transaction log files.
Interestingly, you don’t have to use a differential backup at all. You can work using only a full backup and transaction log backups.
With this understanding of what is needed to restore a database, you can use the database maintenance wizard to create a database backup plan that meets your needs. Ask yourself, what is the longest period of time I am willing to lose data that had been modified? If you can live with an hour, and your database performance is adequate, you could configure your system to take transaction log backups every hour. If your transaction log backup takes too long each hour, you may need to make it more frequent to keep the impact during backups from slowing performance.
This should get you started. If you’re interested, there are many more nuances to the database backup and restore capabilities that may be useful. Regardless, be sure to have something in place, even if it is not the most optimum solution.
Cheers,
Ben