Full backup

A full backup is simply the backup of a complete database. When performing a full backup, SQL Server stores metadata of the database (its name, creation date, all options set to the database, paths to all files belonging to the database, and so on), used data pages of every data file, and also the active part of the transaction log (which means all transactions that are not checkpoint yet and all running transactions even if they are not finished).

At the end of the backup process, SQL Server stores the last Log Sequence Number (LSN) for possible additional backups.

A full backup never clears the transaction log file!

A full backup can be performed with no respect to the recovery model set but the correct option is to have a SIMPLE recovery model if we don't intend to add additional backups to our backup strategy.

The command for a full database backup is generally as follows:

BACKUP DATABASE <database name> TO DISK = '<full path to backup>'

We can write the following as an example to back up an AdventureWorks database:

BACKUP DATABASE AdventureWorks TO DISK = 'D:\myBackups\AdventureWorks.bak'

Let's describe the preceding example in more detail. A database called AdventureWorks must exist on the server and it must be in consistent state; in other words, we never cannot back up a database (with any kind of backup) that is not online and working normally. The second mandatory condition is that the path D:\myBackups must exist on the filesystem. Backups are not installers, they never create folders.

The filename for the backup is arbitrary, the .bak extension is recommended. The file itself need not exist, it's created with the first backup. It's possible to store more backups in one file. When we want to have more backups in one file, we have to add a new option to the backup command:

BACKUP DATABASE AdventureWorks TO DISK = 'D:\myBackups\AdventureWorks.bak'
WITH NOINIT

When the preceding example is run for the first time and the .bak file doesn't exist, it will be created. When the same command is executed a second time with the same path and filename, the backup file will grow in volume because additional backup will be added to it. Recursively, when we want to erase all backups from the backup file and start a new backup cycle, we can change the NOINIT option with the INIT option. The INIT option causes all backups to be erased from the backup file and only the new backup will be stored in it.

Full backups tend to have a big volume. It could lead to disk insufficiency as well as too much time spent by the backup operation. That's why it's good and highly welcome to compress backups. There are two ways for doing this. The first way is to set the server level to default for backup compression. The command for this server setting is as follows:

EXEC sp_configure 'backup compression default', 1
GO
RECONFIGURE
GO

The sp_configure system stored procedure is used in many cases, as seen in the preceding example. The first parameter 'backup compression default' is the name of the configuration property. It's actually hard to remember all configuration parameters, the simplest way is to call sp_configure just as is, without parameters, and the procedure will return the result set with a list of parameter names and currently configured values.

The second parameter (number 1) is a bit value; in this case, indicating that we want to switch the backup compression on. For some configuration values, only bit is used; for example, when setting the maximum degree of parallelism, the integer value indicating how many CPUs can be used for parallel processing of one T-SQL request.

The RECONFIGURE command causes the load of the configured property immediately without the need of service restart.

If we don't need to explore the default server setting of compression, we can simply add another option directly to the BACKUP command:

BACKUP DATABASE AdventureWorks TO DISK = 'D:\myBackups\AdventureWorks.bak'
WITH NOINIT, COMPRESSION

Can you believe how simple it is? Just keep in mind that compressed backups cannot be stored in the same file as uncompressed backups because SQL Server compresses the backup file when it is written. In other words, we cannot have a part of file uncompressed and the rest of the same file compressed. If we don't want to compress some backup, the opposite option is NO_COMPRESSION.

Let's have some uncompressed backup and we want to use backup compression. In this case, the INIT option of the BACKUP command is too weak. We need to replace the INIT option with the stronger FORMAT option. The Format option deletes the backup file and creates a new one. Use the FORMAT option carefully because it will cause all your backups in certain backup files to be lost forever.

A full backup serves as a baseline for more advanced backup strategies. It is often combined with transaction log backups and the dependency is driven by the last LSN written to every backup. When additional transaction log backup is executed, SQL Server remembers the last backup LSN from the previous backup and starts the current backup operation from the next LSN in order. Hence, when a full backup is executed, the last remembered LSN is replaced with a new one and the backup strategy obtains a new baseline.

In some cases, this is undesired behavior. For example, in situations when we need to create a copy of a certain database with a full backup, but without breaking out of the backup sequence. For this case, one more full backup variant exists:

BACKUP DATABASE AdventureWorks TO DISK = 'D:\myBackups\tempBackupOfAdventureWorks.bak'
WITH COPY_ONLY

The COPY_ONLY option in the preceding command causes that the LSN sequence tracked for backup consequences is not restarted and the exceptional full backup doesn't establish a new baseline for the backup strategy.

Full backup is relatively straightforward but less efficient when we need to minimalize potential data loss. That's why we need to have some stronger mechanism on how to keep our data safe and sheltered against physical as well as logical damage.