Compressed Microsoft SQL Server Backups by Default

Backup Overview (SQL Server)

back up [verb]
Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.

backup [noun]
A copy of SQL Server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log.

recovery model
A database property that controls transaction log maintenance on a database. Three recovery models exist: simple, full, and bulk-logged. The recovery model of database determines its backup and restore requirements.

A multi-phase process that copies all the data and log pages from a specified SQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.

Types of backups

copy-only backup
A special-use backup that is independent of the regular sequence of SQL Server backups.

data backup
A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

database backup
A backup of a database. Full database backups represent the whole database at the time the backup finished. Differential database backups contain only changes made to the database since its most recent full database backup.

differential backup
A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data extents that have changed since the differential base.

A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.

full backup
A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

log backup
A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)

file backup
A backup of one or more database files or filegroups.

partial backup
Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.

Compressed Microsoft SQL Server Backups by Default

If you compress your database backups you can save a ton of disk space. But by default compressed backups are not created. In order to create a compress SQL Server database backup when your default is not compressed you need to add the “WITH COMPRESSION” option to your BACKUP command. Not a big deal to do, but if you want to always create a compressed backup it might be better to change the default backup type to be compressed. By doing that all our backup command that forget to add the “WITH COMPRESSION” option will be compressed by default.

Your default backup compression option is determined by a system configuration. You can determine how your system configuration is set for the default compression by running the following command:

EXEC sys.sp_configure N'backup compression default';

When you run the above command you will get some output that might look like this:

Here you can see the configured and run value for my backup compression default setting. Note that currently my backup compression default is set to zero (0). This means by default my backups are not compressed. Therefore when a BACKUP command is run that doesn’t have the WITH COMPRESSION option defined I will get an uncompressed backup.

To change the default backup compression options to be compressed you just need to run the following two statements:

EXEC sys.sp_configure N'backup compression default', N'1'

By running these two command I have changed the default “backup compression default” value to 1. This is the value that will enable compressed backups by default.

Rate this post