There are three main ways that your database can fail. Your job as SQL Server database administrator is to anticipate and plan for these inevitable database failures.
You should constantly 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 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. 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.
* Recovery Models
Full Recovery Model
The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.
For heavy OLTP databases, there is overhead associated with logging all of the transactions, and the transaction log must be continually backed up to prevent it from getting too large.
Benefits:
* Most resistant to data loss
* Most flexible recovery options - including point in time recovery
Disadvantages:
* Can take up a lot of disk space
* Requires database administrator time and patience to be used properly
Bulk-Logged Recovery Model
The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.
The following transactions are minimally logged in a Bulk-Logged Recovery Model:
* SELECT INTO
* bcp and BULK INSERT
* CREATE INDEX
* Text and Image operations
Benefits:
* Transaction log stays small
* Easier from an administration standpoint (don’t have to worry about transaction logs)
Disadvantages:
* Not for production systems
* Point in time recovery not possible
* Least data resistant recovery model
Simple Recovery Model
The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.
Benefits:
* Transaction log stays small
* Easier from an administration standpoint (don’t have to worry about transaction logs)
Disadvantages:
* Not for production systems
* Point in time recovery not possible
* Least data resistant recovery model
What recovery model is my SQL Se rver 2005 database using? It is easy enough to find out by following the steps below.
The recovery model can be determined in SQL Server 2005 by accessing the properties of the database. To do this, you can right click on the database in Object Explorer and select "properties" as shown below.
After right clicking on properties, the SQL Server 2005 Database Properties window appears. Left click on options. The recovery model will be revealed to the right. In the screenshot below, you can see that the database is using the Full Recovery Model.
