As we continue unpacking the different options of the Database Maintenance Plan Wizard we come to the topic of Backups. The maintenance plan wizard has three kinds of backups. A complete database backup, an incremental database backup, and transaction log backups.
This is a big topic because the way you utilize all of these different backup capabilities is determined on a database by database basis, driven by the amount of risk you are willing to accept for data loss, and database performance.
A complete database backup is the cornerstone of all database recovery plans using a database restoration from a backup. You can have a database recovery plan based primarily on a complete database backup. This means that you would simple restore your database from the complete backup. If you chose this model there are a few things you’d need to do as well.
First, you will want to be sure that a complete backup can be completed in your backup window. Second, you will need to back up your transaction log, even if you don’t use it, as a means of clearing out your transaction log files for future activity. You’d want to do this at least as often as you backup your database. Finally, you are going to want to test restoring your database from the complete backup in order to make sure it can be performed within an acceptable time frame.
Tomorrow we’ll unpack the incremental backup and transactional backups along with how they are used to optimize database restoration.