We’ve been talking about how to create and perform automated database maintenance tasks for the non-DBA individual. Primarily, we have been focusing on tasks that can be performed real time, or scheduled to be done during hours when the database is less active, such as updating statistics, or defragmenting indexes.
However, there are some maintenance tasks you really need to do for which there is no other alternative, and, even if you are not a DBA, you need to understand how they work, or you are at risk for loss of data. To use an analogy, you can sometimes make warning lights go away on a car by disconnecting and re-connecting the battery. When you do this, the car appears to be working smoothly until the error that caused the light to appear occurs another time. The correct action, instead, is to read the code that caused the light to come on in the first place, and resolve the underlying problem. In short, you really need to understand what is being done, or pay to have someone do the task correctly. You don’t want to hide the event.
The same thing is true in any database in which you cannot afford to lose data. You must have a backup and restore strategy. The database maintenance wizard has three options for database backups, and you need to understand what they do in order to use them effectively. The wizard will create the following backup commands so you don’t have to know the TSQL to enable the backup:
- Full Backup
- Differential Backup
- Transaction Log Backup
The full backup takes the longest to execute. When run, it makes a complete copy of the database. It can be run without taking the database offline. So, it will not necessarily capture all work in flight during the backup.
A differential backup simply makes a backup of changes that have occurred in the database since that last full backup or differential backup was completed.
A transaction log backup will make a backup of changes since the last transaction log backup or full backup was performed.
Which backup type should you use? Should you use more than one backup type? The answer to this question will remain until the next editorial. It is based on your answer to the question, how much data are you willing to use should your database become corrupt, and, how long does it take to perform the different backup types.
In summary, wizards do help you with Syntax. They do not replace the knowledge you need to gather in order to use the tools correctly.
Cheers,
Ben