Introduction

This is a quick guide to the more popular SQL Server backup routines. It isn’t really intended for experienced professional DBA’s, but in many cases the DBA role of installing SQL Server and setting up backup routines is not actually carried out by DBA’s. But rather the responsibilities for this are managed by Server / Infrastructure Engineers or Developers.

Now we aren’t implying that people in these roles are not capable of backing up a database, but beyond the full backup schedule the waters can become a bit muddy in relation to when to use differential backups and / or transaction log backups.

Below is a summary of Full, Differential and Transaction Log backups. Part 2 of the blog will go through how to check what backups have taken place, backups carried out by third party products etc.

Full Backups

As the name implies a full backup backs up all of the database to a file, no great surprises there. If a conventional SQL Server backup routine is used as opposed to using third party backup software such as CommVault or Veam, then the database will be backed up to:-
Local disk on the database server
Network location such as a shared Windows folder. (The user running the SQL Server service will need write access to the shared folder) tape device (although not sure how often databases are backed up directly to tape nowadays, it’s usually first to disk) Azure backup location
The standard SQL Server backup file is a single file, normally with a .BAK extension. This file can then be restored over the top of the existing database or restored on another server that is running the same or later version of SQL Server.

Differential Backups

A differential backup or sometimes called incremental backup, works in the same way as the full backup, but it simply backs up all of the changes that have been made to the database since the last full backup took place.
For example, if a full backup is taken at 01:00 and then a differential backup at 09:00, the differential backup would contain the database changes that have been made in the last 8 hours. Likewise if a further differential backup is taken at 17:00, the backup file would contain the database changes that have occurred in the last 16 hours.

There is no relationship or dependency with the two differential backup files. So if you needed to restore the differential backup that was taken at 17:00 it would just be a case of restoring the full backup that was taken at 01:00 (with norecovery) and then restoring the 17:00 differential.

All differential backups have a relationship and dependency on the last full backup that was taken.

Transaction Log Backups

Transaction log backups can only be done when the database is in Full recovery mode. The other two modes are Bulk Logged which is rarely seen and Simple recovery which broadly speaking is usually used for test and development environments or other systems which would never require a point in time recovery.

The transaction log backups contain the changes that have been made to the system. But unlike differential backups the transaction log backups contain the changes that have been made since the previous transaction log backup.
Going back to the previous example of a full database backup taking place at 01:00. For the sake of illustration there is then a transaction log backup occurring at 02:00 and then again on an hourly basis until 17:00. Each backup would contain the changes to the system that have taken place in the previous hour.

If you wanted to restore the database to 17:00 you would restore the full backup (with norecovery), then restore each of the transaction logs in turn. All would be restored with norecovery except the last one which would be restored with recovery (the default setting).

Unlike differential backups, transaction log backups are dependent on each other as they form a chain. So if any of the transaction log backups were lost then the recovery would not be able to go any further.

Transaction logs also allow you to restore to a particular point in time (POITR). So for example if someone accidentally dropped or deleted from a table at 16:34, then the database could be restored on the same or different server to 16:33.

Backup Compression

In SQL 2008R2 Standard edition and later, all conventional SQL Server backups can be compressed. The compression ratio can vary depending on what type of data is being held in the database. Tables holding BLOB Images such as files or images will not compress as well as tables that hold text and numeric data. If you are lucky you could see the size of the backup file reduce to the region of 10 – 15% of the original backup size.

Compression can be selected as an option when the backup is done, or the default compression (is it On or Off) can be set at the SQL Server level and this setting will be used when no compression options are specified.

Command Line Backups

Although it is possible to carry out all of these commands by using the GUI within SSMS, once you are used to command line backups they are more convenient.

Simple full backup

BACKUP DATABASE Prod TO DISK = 'C:\Backups\Prod.bak'

Full backup using compression

BACKUP DATABASE Prod TO DISK = 'C:\Backups\Prod.bak' 
WITH compression

Backup to a network share (SQL Server Service Account needs write access to share)

BACKUP DATABASE Prod TO DISK = 
'\\FILESERVER1\BACKUPS\Prod.bak'

Full backup which also overwrites any backups with the same name

BACKUP DATABASE Prod TO DISK = 'C:\Backups\Prod.bak' 
WITH compression, format

Full backup which also overwrites any backups with the same name and shows progress in 1% increments

BACKUP DATABASE Prod TO DISK = 'C:\Backups\Prod.bak' 
WITH compression, format, stats=1

Differential Backup

BACKUP DATABASE Prod TO DISK = 'C:\Backups\Prod.bak' 
WITH differential

Transaction Log Backup

BACKUP DATABASE Prod TO DISK = 'C:\Backups\Prod.trn'

Log Backup with Date and Time appended to filename (i.e. Prod_2017-10-16 13_49_35.trn)

DECLARE @date VARCHAR(128) SELECT @date = 
CONVERT(CHAR(128), GETDATE(), 120) SELECT @date = 
'C:\Backups\Prod_'+ REPLACE(@date, ':', '_') + '.trn' 
BACKUP LOG Prod TO DISK = @date

Bobby Balmer

This article by Bobby Balmer was originally published by SQL Server experts, Intersect Consulting. You can view more of their articles on their blog.