A SQL Server database can be configured to use one of three recovery models: Simple, Full, or Bulk-Logged. It is up to each customer to examine the benefits and drawbacks of each model and determine which is most appropriate for their needs.
|
Recovery Model |
Description |
|---|---|
|
Simple |
The Simple recovery model requires the least administration by a Database Administrator. With this model, a database can be recovered only to the point of the last full or differential backup. It is not necessary to perform backups of the transaction log, as data is truncated from the log after it is written to disk. While this model provides easy administration, it comes at the expense of more data loss if the database files are damaged. If your implementation requires little or no data loss, this is not the appropriate option. |
|
Full |
The Full recovery model provides much more data protection, but also requires more administration and more disk/tape space. When a database is operating in full recovery model, all committed transactions exist in the database transaction log until the log is backed up. In the event of a database failure, the last full backup of the database is restored, followed by every subsequent backup of the transaction log to the most recent point in time. The transaction log can be backed up as frequently as needed (for example, every four hours, every hour, every fifteen minutes, etc.). Note:
The transaction log, if not backed up on a regular basis, will continue to grow and consume all available disk space. When there is no more disk space available, the database will become unavailable. |
|
Bulk-Logged |
The Bulk-Logged recovery model is similar to Full recovery mode in that the transaction log is not truncated until it is backed up, but bulk operations (for example, SELECT INTO, BULK INSERT) are not logged. This option is not recommended for an OnBase system. |