A SQL Server backup is one of the most important features DBAs should fully understand and use. It’s not uncommon that the process of backing up SQL Server data is considered as simple and therefore easily overlooked as essential when it comes to disaster or other recovery scenarios. Realizing that a backup is not valid (outdated, corrupted, or even does not exist) at the moment when it’s needed is highly undesirable scenario for any DBA.

SQL Server backups provide the possibility to recover a damaged database or to restore a database to a point-in-time. There are a number of scenarios which can cause database corruption or inaccurate data which require data recovery. However, each environment, in which SQL Server operates, may require a different backup and recovery strategy, adapted to available resources. The strategy should minimize data loss possibilities and maximize data availability at the same time, while considering specific enterprise requirements (e.g. which and how much data should be backed up, how long backups should be retained, etc.).

SQL Server database recovery models

When it comes to SQL Server backups, it is not possible to plan a backup strategy without fully understanding database recovery models. The recovery model define how SQL Server logs transactions performed on a database, which determines available backup operation types for the database backup process.

The database recovery model option is available in the Database properties dialog, within the Options page. There are three available recovery models: Full, Bulk-logged, and Simple.

SQL Server system databases are specific regarding recovery models. For example, master, msdb, and tempdb databases are tied to the Simple recovery model, while the model database can use any of the recovery models. Note that the recovery model used for the model database determines what recovery model will be used for newly created user databases by default.

Full recovery model

If the Full recovery model is set for a database, all transactions are fully logged into the database transaction log. The Full recovery model offers the widest flexibility for backup strategies.

Bulk-logged recovery model

The Bulk-logged recovery model is very similar to the Full recovery model with the difference in the way some specific operations are logged into a database transaction log. Such operations are minimally logged, resulting in a reduced disk space usage by the database transaction log. Other transactions are fully logged. In addition, this recovery model improves SQL Server performance comparing to the Full recovery model since only the extent allocations are logged. However, there is an important downside of this recovery model. There is an increased data loss risk since it’s not possible to perform a database point-in-time recovery if a transaction log backup that contains bulk-logged operations is used for recovery. If that’s the case, the whole transaction log backup must be restored.

The following operations are considered as bulk operations and are minimally logged when using the Bulk-logged recovery model: BULK INSERT, operations executed via BCP command prompt utility, partial updates to large data types using the .WRITE clause, CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX (in case a heap rebuild is required), and OPENROWSET with the BULK rowset provider

Simple recovery model

The Simple recovery model should not be used in production environments where database recovery using transaction log backups may be required. If used, this kind of recovery model does not allow creation of transaction log backups. This is caused by the fact that the transaction log holds only the records which describe applied changes until a checkpoint has occurred, and the data is written to disk.

While not being suitable for production databases, this recovery model, on the other hand, provides maximum SQL Server performance gain of all three recovery models.

Note that the combination of the Full and Bulk-logged recovery models can be used. While using the Full recovery model, you can switch to Bulk-logged (e.g. prior to executing large bulk operations), and then switch back to Full. However, you should backup the database transaction log each time prior to changing the recovery model. This method improves SQL Server performance during a bulked operation execution, decreases required disk space needed for transaction log backups, and provides higher number of transaction log backups that can be used for a point-in-time recovery.

SQL database backup types

There are several SQL Server database backup types. A database backup can, but does not have to, contain entire database. This depends on the backup type and your backup strategy which can involve different backup types. It’s recommended to start any backup strategy by creating a full database backup.

Full SQL database backup

A full backup type holds the entire database and the part of the transaction log containing uncommitted transactions that will be rolled back or replayed during the recovery process in order to make data consistent upon restore. This is required as SQL Server does not pause activities in the database while it is being backed up, which means that the database can be modified during the backup process.

The full backup serves as the starting point for subsequent differential and transaction log backups. Moreover, differential and transaction log backups cannot be created if the full database backup has never been created.

A typical scenario for a full database backup creation is once per day, followed by transaction log backups during the day. In case full database backups are large, full backups should be taken in larger time frames (e.g. once per week), and supplemented with differential backups the rest of the time. Whatever strategy you choose, it starts with the full database backup.

Note that SQL Server backs up only the active/used data pages, meaning the backup can be significantly smaller than the actual online database.

Differential SQL database backups

In case only the changes applied after the last full backup was taken need to be backed up, use the differential database backup type. This is exactly what a differential database backup contains. It uses a bitmap page where each bit contains information about every extent in order to track the changes.

Each differential backup contains all changes that occurred since the last full backup. This means that other previously created differential backups may be considered as obsolete. Also, in case of databases with a large number of changes, differential backups can easily become larger than the database itself. To overcome this and to benefit from the use of differential backups, it’s recommended to take full backups in intervals short enough to keep differential backups smaller than the full backup.

Differential backups are recommended for databases with infrequent changes. It’s recommended to start with the full database backup followed by scheduled transaction log and differential backups. So, in case the recovery is needed, it’s enough to recover only the full backup, the last differential backup, and transaction log backups created after the differential backup up to the required point in time.

SQL Server Transaction log backups

Transaction log backups provide maximum data protection in case of a database disaster. These backups should be used along with the full and bulk-logged backups. Along with the data protection, transaction log backups allow transaction log to be truncated enabling reuse of unused space for new transactions. In case the transaction log has never been backed up, it can grow until it occupies free disk space.

Note that you cannot create a transaction log backup if the database uses the Simple recovery model. In such case, SQL Server truncates the log on checkpoints automatically.

A transaction log backup contains only transactions that occurred after the last transaction log backup was taken. A sequence of transaction log backups taken one after another, after the full of bulk-logged database backup creates a full log chain. The chain allows database restoration to a point-in-time, presuming the last transaction log backup doesn’t contain any of minimally logged operations.

If, for any reason (e.g. a corrupted backup, missing backup file, or used Simple recovery model in the log chain time frame) the chain is broken, later transactions in the chain cannot be recovered.