Category: Recovery Models

In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows the creation of full, differential, and transaction log backups thus making point-in-time recovery possible.

You need to keep in mind that in the full recovery model, the transaction log file would be much larger, and you have to make regular log backups to keep it small. Read More

The simple recovery model allows full and differential database backups only, and there is no chance of making transaction log backup. At the time when a checkpoint is created in a simple recovery model, all committed transactions are removed from the transaction log. That means that point-in-time recovery isn’t possible. Read More

The bulk-logged recovery model is designed for intermittent use to improve the performance of bulk imports of large amounts of data. It’s practically the same as the full recovery model with the only exception that under the bulk-logged recovery model some operations are logged minimally. These operations are:

• Bulk import operations (bcp, INSERT … SELECT, and BULK INSERT).
• SELECT INTO operations.
• Partial updates to large value data types using the WRITE clause in the UPDATE statement when inserting or appending new data. Please note that minimal logging is not used when existing values are updated.
• Inserting or appending new data into the text, ntext, and image data type columns using the WRITETEXT and UPDATETEXT statements. Please note that minimal logging is not used when existing values are updated.
• If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged regardless of whether the operation is executed online or offline. (Minimally logged index operations are as follows: CREATE INDEX, ALTER INDEX REBUILD or DBCC DBREINDEX and DROP INDEX).

Read More