Recovery Models

Microsoft has grouped the various strategies that can be employed into three stereotypes or models: simple, full, and bulk-logged. These SQL Serverspecific models not only help you think about your needs, but also simplify the implementation of the appropriate strategy.

SQL Server 2000 has three models: simple, full, and bulk-logged. Let's look at what each model means, starting with the easiest.

• Simple: The simple model describes just that: the simplest and easiest situation to manage. When a database is set to this model, there is no way you can back up only the changes made since the last backup. Instead, only full backups are allowed. One benefit of this model is that the transaction log won't become full from transactions occurring between the full backups. Whenever the database performs a checkpoint, space in the log is reclaimed. Additionally, non-logged operations such as bulk copy are permitted.
• Full: The full model allows you to create not only complete backups of the database, but also incremental backups of only the changes that have occurred since the last full backup.
  
  An added benefit is that it's possible to recover the database to a particular point in time. For example, if a user accidentally deletes all accounts in a database at 1 PM, it's possible to restore the database up to 12:59 PM, right before the deletion of the accounts occurred.
  
  Under this mode, space in the transaction log is only reclaimed when a backup of the transaction log is made. When this occurs, all the changes stored in the transaction log are written to the backup and the space is freed up. Therefore, databases in this mode need to have enough space available for the transaction log to store all the transactions that occur between each backup. Additionally, non-logged operations are not allowed.
• Bulk-Logged: The bulk-logged model lies between the other two models. On the one hand, incremental backups of the database are possible. The transaction log is treated the same way in this model as in the full model. However, bulk copy operations are only minimally logged. Instead of logging each insert into the table, SQL Server only logs the minimum necessary to recover the data if the backup is needed. However, because of this, if a bulk copy operation occurs, point-in-time recovery (described in the previous paragraph) is not possible. Recovery can only stop at the end of a transaction log.