How to Backup a SQL Server Database

This post contains a demo on how to backup a SQL Server Database and includes some information on the types of backups available in MS SQL.

The 3 types of backups that will be covered in this post are:
# MS SQL Full Backup
# MS SQL Transaction Log Backup
# MS SQL Differential Backup

Before backing up a database, you should verify the disk the backup is going to has enough space to hold the backup. You may also want to check when your databases were last backed up, to ensure running a backup is necessary at this time, or for general review.

There’s a lot to understand with backups in SQL Server. Always refer to MS Docs when performing a change to a Production environment. This is just demo & information sharing.

MS SQL Full Backup

A Full Database Backup is a backup that will contain the whole database, all of the data included until the point in time the backup was taken. This is our main Restore Point.

By running the command below, we are able to run a Full Backup of the database, with compression.

Amend the database name in [square-brackets] and location/name on disk in ‘quotes’ –

-- Run full database backup with compression
BACKUP DATABASE [demoBlog] TO DISK = N'd:\D:\mssql_backups\demoBlog_full_01082022_1300.bak' WITH COMPRESSION, STATS;

Backing up a database can sometimes take a very long time. It depends on various factors why a backup takes longer, but one common factor is, that the bigger your database gets, the longer it will take to backup & restore.

I’ve seen a Full Backup in a Production environment take more than a day to complete (multi-terabyte). This is why a script to check estimated backup & restore time comes in useful for a DBA.

MS SQL Transaction Log Backup

Transaction Log Backups in SQL Server help log & store transactions that are made in your database which can then be used to bring databases back online to a point in time, in a disaster scenario.

When running the command below we want to have an associated Full Backup that chains to this Transaction Log Backup. That means, having a Full Backup you performed, and your LSN chain hasn’t been broken by a manual Full Backup without including the Copy_Only parameter.

To run a Transaction Log Backup –

-- Run tlog backup 
-- (amend database name & location/file on disk)
BACKUP LOG [demoBlog] TO DISK = N'd:\mssql_backups\demoBlog_tlog_21082022_2130.bak' WITH COMPRESSION, STATS;

Using the above Transaction Log backup, we can restore data that happened after the last Full Backup up until the point in time this Transaction Log backup was initiated.

The reasoning behind using Transaction Log backups generally comes down to your business’s desire to ensure High Availability in your SQL Server environment. If you don’t need a recent restore point and can live with (e.g.) daily Full Backups, Transaction Logs are not for you.

MS SQL Differential Backup

Differential Backups capture only the data that changes since the last Full Backup has run. A Full backup can be considered the base of a Diff Backup (except for Copy_Only backups).

The Differential Backup is one I see rarely in Production environments. A major benefit of Diffs is that they are quick to run. The size of a Diff Backup depends on the volume of data that has changed since its last rebase (last Full Backup).

In the example below I’m running a simple Differential Backup of my demoBlog Database –

-- Run diff backup
BACKUP DATABASE [demoBlog] TO DISK = N'd:\mssql_backups\demoBlog_diff_21082022_2133.bak' WITH DIFFERENTIAL, COMPRESSION, STATS;

That’s us done, a quick review of the main types of database backup in SQL Server. For backup tips, check out the Database Backups tag.

SQL DBA Blog