There are several means by which SQL Server databases can be recovered. The recovery model set for the individual database determines the types of recoveries that can be performed and the amount of work and space required to protect the data.
To understand how database backup works, there must first be an understanding of the relationship between the transaction log and the database files. The transaction log is a copy of every change that occurs to the data (and sometime structure) of a SQL Server database. Every insert, update and delete is first written to the transaction log. Once written (and thus stored) the database determines if the change can be executed. This check involves assuring that triggers can run and all the changes within a group (called a transaction) can be executed. If they can be, the changes are marked as committed. If an error is encountered, the changes are marked for rollback. SQL Server periodically takes the committed changes in the transaction log and writes them to the data file for permanent storage.
The following list summarizes the recovery models:
- Simple – In this mode, transactions are discarded once they have been properly written to the database.
- Keeps the transaction log small and minimizes administration
- The database cannot be recovered except to the point of the last full or differential backup
- Simple recovery is commonly used on developmental databases, read-only databases and databases that receive only occasional updates. When the database is updated, it should be backed up or the changes are at risk of being lost
- Full – In this mode, every transaction is stored, even after the data has been placed in the data file.
- Provides a full record of changes that have been made to the database
- The transactions can be moved to other databases so they can make the same changes
- Also allows point-in-time recovery.
- Full recovery is recommended for transactional databases to provide better control over restoration options. It requires the scheduling of transaction log backups and the management of those files.
- Bulk-logged – In this model – the logging of specified bulk operations are ignored when using bulk insert or bcp that is the transaction, but not the individual changes executed within the transaction.
- When large imports of data are made, this can control the size of the transaction log and improve the speed at which the data is imported.
- I recommended that production databases typically be set in this mode during a bulk load, and then returned to full recovery for normal operations.