Hungry DBA .com for DBAs who like food

SQL Server Backups

Recovery Models

SQL Server Databases can be configured to use different recovery models, which can restrict the type of backups that are available.

There are 3 recovery models available: Full, Simple, and Bulk-Logged. 

Full allows for full backups, differential backups, as well as transaction logs backups. This is the only model that can be used to perform recovery up to the point of failure.

Simple allows for full backups and differential backups. There are no transaction log backups thus you can only recover to the point of the last full or differential backup.

Bulk-Logged allows for full backups, differential backups as well as transaction logs backups. This can also provide recovery to the point of failure on the condition that a bulk operation was not performed during the last (current) transaction log.

The Bulk-Logged recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations. It only performs minimal logging for operations such as: select into, bulk load operations (bcp and bulk insert), create index (including indexed views), text and image operations (writetext and updatetext).

Though the Bulk-Logged recovery model only provides minimal logging for the operations described above (the Full recovery model performs complete logging for these operations) the operation is still fully recoverable because SQL Server keeps track of what extents were actually modified.

Transaction Logs

Transaction logs are very similar to the concept of redo logs in Oracle. Any database changes are continuously logged to the transaction log so that they can be re-applied if necessary during database recovery.

A transaction log, like SQL Server data files, can have its growth set to unrestricted or to a maximum size. Once a transaction log is backed up it is automatically truncated. If the transaction log is never backed up it will continue to grow until it consumes its disk resources or reaches the maximum size (if set). Either way the database will be unusable in this state.

Therefore it is recommended that the transaction log be backed up on a regular basis. This type of backup consumes less resources including disk space than full backups 

Transaction Log backups should be used in conjunction with full backups thus after recovering the last full backup all proceeding transaction log backups are applied to bring the system up to date.

Though you can have multiple transaction logs this only provides a means of adding extra disk space to the transaction log rather than for resilience purposes. Therefore it is essential that the transaction logs be placed on mirrored disks.

Full Backups

Full backups are available with all types of recovery model.

It is as the name suggests a complete backup of a SQL Server database.

However, a full backup does not include a backup of the relevant databases’ transaction log.

Differential Backups

A differential backup is an optional backup that is also available with all types of recovery model.

Again as the name suggests it will only backup any changes made since the last database backup thus save on system resources.

Database Recovery

Full Recovery is performed by first backing up the current transaction log, applying the last full or differential backup, then applying all transaction log backups (including the last backup of the current transaction log) that were performed after the last full or differential. If a transaction log is lost or damaged then you can only recover to the point in time before that transaction log occurred.

During recovery it is necessary to back up the current transaction log in order to perform recovery to the point of failure. In the Bulk-Logged recovery model, if a bulk operation has been performed, then that transaction log backup requires access to all of the database data files. If they are not available then it is not possible to backup the current transaction log thus recovery to point of failure is impossible.

To recover the master database the SQL Server instance has to be brought into single user mode, this can be achieved by restarting SQL Server with the ‘-m’ start-up option. If the master database is damaged, thus SQL Server cannot be started, you can recreate the default databases via the REBUILDM utility. This utility recreates the master, msdb, and other default databases from the original installation CD. Once the database is rebuilt you can then recover the master, and other default databases, from your backups.

Database Maintenance Plans

SQL Server provides database maintenance plans as an ease-of-use mechanism for managing the databases including backups.

A maintenance plan can be created for a single database, all databases, all system databases, or all user databases (non-system databases) thus they are excellent facility for managing an environment that contains many databases.

The only types of backups that can be performed from database maintenance plans are full and transaction log.

Other maintenance jobs that can be setup via a database maintenance plan include Optimisations and Integrity Checking. 

Best Practices 

Configuring Backups

All production SQL Server user databases should be configured to use a full recovery model (unless they are read only), thus to ensure the database can be recovered up to the point of failure.

Ideally full and transaction log backups of a SQL Server database should be made via a database maintenance plan to local disk. These backups can then be copied onto tape during the normal operating system backup. Using this mechanism to backup the SQL Server databases provides greater control to the DBA for managing backups. However this may not be possible due to limited disk space.

Separate database maintenance plans should be created for System and User databases, mainly because the master database cannot perform transaction log backups.

An operator should be created for the relevant DBA to receive an email report on whether the backups were successful. This can be configured through the reporting tab of the database maintenance plan. The reporting tab section should also be used to specify a directory for writing logs.

Should limited disk space make it impossible to backup databases to local disk then it would be more appropriate to use a third party tool (such as Tivoli or Veritas Backup Agents) to backup the relevant databases directly to tape. The problem with using this mechanism is that it takes control away from the DBA for performing backup and recovery as this is then all performed from the backup tool. These backup agents, however, do usually support full and transaction log backups.

In general a full database backup of the system databases (master, msdb, model and distribution) should occur nightly. A full backup of user databases should occur at least twice per week with transaction log backups occurring every other day. However depending on the level of transactions that occur on a particular database it may be necessary to perform transaction log backups more frequently in order to reduce log growth.

Monitoring Backups

It is possible to check the date and time of the last database and transaction log backups (whether performed via SQL Server or a third-party tool) through the General tab of an individual database’s properties within Enterprise Manager.

If a backup is triggered from a job, including from database maintenance plans, notifications should be configured to email the relevant operator with the result of the backup. It is also possible to view the job history via Enterprise Manager.

Logs can also be viewed, as specified in the Reporting tab of the database maintenance plan, to check whether the backups were successful and obtain more details should a backup fail.

It is also possible to check job history by running the stored procedure SP_HELP_JOBHISTORY against the MSDB database.