How to Backup a SQL Server Database

This guide provides a simple demo on how to back up a SQL Server database and explains three main types of backups in MS SQL:
> SQL Server Full Backup
> SQL Server Transaction Log Backup
> SQL Server Differential Backup
> Additional Notes on SQL Server Backups

Before starting, make sure there’s enough space on the disk where the backup will be stored. It’s also a good idea to check the last backup date for the database to confirm if a new backup is needed if doing this for disaster recovery purposes, the backups should already be happening on a schedule if this is needed.

Always refer to Microsoft’s official documentation before making changes to production environments. I’ll link to MS Docs throughout this article.

MS SQL Full Backup

A Full Backup captures the entire database, including all the data up to the time the backup is taken. It serves as your primary restore point.

To run a Full Backup with compression, use the following command. Update the database name in [square brackets] and the backup file location in 'quotes':

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;

Note: Backup duration depends on the database size. For large databases, backups can take several hours or even days in production environments. Consider using a SQL script to estimate backup and restore times.

MS SQL Transaction Log Backup

A Transaction Log Backup records changes made to the database since the last Full Backup. It’s useful for restoring databases to a specific point in time in disaster scenarios.

When running a Transaction Log Backup, ensure the backup chain is intact. Avoid breaking the chain with manual Full Backups unless using the COPY_ONLY option.

Here’s the command to back up a transaction log:

-- 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;

Use Case: Transaction Log backups are essential for environments requiring high availability. If your business can tolerate only daily Full Backups, Transaction Logs may not be necessary.

MS SQL Differential Backup

A Differential Backup stores only the data that has changed since the last Full Backup. This makes it faster to run compared to a Full Backup. Differential backups are less common in production but can be useful for quick interim backups.

Run a Differential Backup with this command:

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

Key Point: The size of a Differential Backup depends on how much data has changed since the last Full Backup.

Additional Notes on SQL Server Backups

Backing up your SQL Server databases is essential for maintaining data integrity and disaster recovery capabilities. Here are some best practices to consider:

> Schedule Regular Backups: Create a backup schedule that fits your business needs, combining Full, Transaction Log, and Differential backups as appropriate.
> Test Restores Frequently: Ensure that backups are restorable by regularly testing restores in a non-production environment.
> Monitor Backup Sizes and Durations: Keep an eye on how long backups take and their storage requirements to avoid surprises.
> Use Compression: Enable backup compression to save space and speed up the process.
> Run Backups During Off-Peak Hours: Schedule backups when database usage is low to minimize performance impact.
> Back Up to Multiple Locations: Store backups in different locations (e.g., on-premises and cloud) to protect against hardware failure or disasters.
> Use Readable Secondary Replicas: If you’re using Availability Groups (AG) with readable secondaries, offload backup tasks to those replicas to reduce the load on your primary server.

Following these practices will help you ensure that your backup strategy is robust, efficient, and aligned with your business recovery objectives.