Author: Alexandr Omelchenko

Full Recovery Model

Alexandr Omelchenko

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

Simple Recovery Model

Alexandr Omelchenko

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

Transaction Log Backup

Alexandr Omelchenko

A transaction log backup contains all transaction log records that have been made between the last transaction log backup or the first full backup and the last log record that is created upon completion of the backup process. The transaction log backup allows to restore a database to a particular point-in-time before the failure has occurred. It is incremental, meaning that in order to restore a database to a certain point-in-time, all transaction log records are required to replay database changes up to that particular point-in-time. Please note that transaction log backup is available only for full or bulk-logged recovery models. The picture below shows how transaction log backup works.

Transaction log backup Read More

Bulk-logged Recovery Model

Alexandr Omelchenko

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

COPY_ONLY Backup

Alexandr Omelchenko

In order to understand how to operate the “COPY_ONLY” option and its nature, let’s consider the following example. Assume that a full database backup starts at 00:00 once a day and a differential backup starts every six hours. In the picture below you can see what database changes (marked as pages) are included into each of database backups shown on the picture.

Backup (Full+Diff) (1) Read More

Checkpoint

Alexandr Omelchenko

Checkpoint is a process that writes current in-memory dirty pages (modified pages) and transaction log records to physical disk. In SQL Server checkpoints are used to reduce the time required for recovery in the event of system failure. Checkpoint is regularly issued for each database. The following set of operations starts when checkpoint occurs:

  1. Log records from log buffer (including the last log record) are written to the disk.
  2. All dirty data file pages (pages that have been modified since the last checkpoint or since they were read from disk) are written into the data file from the buffer cache.
  3. Checkpoint LSN is recorded in the database boot page.

Checkpoint Read More