SQL Server 2005 Types of Database Failure

The SQL Server 2005 Database Administrator's Worst Nightmares

As a SQL Server 2005 DBA, you're going to need to be able to recover from the following circumstances.

You should contantly be testing your backup and recovery plan against simulations of the following:

  1. User error - This accounts for either an application or a user incorrectly modifying or destroying data within the database. In this case, the decision may be made to take the database back to a prior point in time. In the event that this occurs, the DBA must be perform a recovery and restore the database to the state it was in prior to the user error occurring. Obviously, the DBA must prepare for this event - which will almost certainly happen when it is least expected - ahead of time. Be sure to include potential user or application errors as part of your backup and recovery testing and overall plan.
  2. Media Failure - This occurs when one or more drives used by the database fail. If you're lucky enough to have a redundant system (like Raid 1), you may never encounter this situation. You still shouldn't be complacent though. Safeguards such as actually being notified of disk failure don't always work as advertised (as I have personally witnessed). In that case, you have two disks fail before you realize that the database is down. Having a sound backup and recovery plan involving the full recovery model that is tested frequently is the key to recover from such a disaster.
  3. Catastrophic event - A major event destroys the data center. To prepare for this situation, be sure that you have a plan in place to copy your backups off-site in a safe location.

The DBA will be responsible for protecting the data from these events and restoring and recovering the databases if such an event occurs.