In the world of database management, ensuring the safety and integrity of your data is paramount. One crucial aspect of this is regularly backing up your SQL Server databases. In this guide, we’ll walk you through the process of backing up a database in SQL Server using SQL commands.

Performing a Full Backup of Database

In the example below, we’re performing a Full Backup of the ‘Jupiter’ database to a designated disk location. This is a simple command that DBAs will write freely and very quickly without needing to refer to MS Docs for db backups.

For this example, I’m adding <WITH COMPRESSION, COPY_ONLY, STATS=4> as parameters.

USE master;
GO
-- Execute Full Backup with specified options
BACKUP DATABASE Jupiter
TO DISK = 'D:\mssql_backups\Jupiter_FullBackup.bak'
WITH 
    COMPRESSION,                    -- Use compression
    COPY_ONLY,                      -- Create a copy-only backup
    STATS = 4;                      -- Display statistics during the backup operation

Full Backup Set Options in Demo

COMPRESSION
This statement allows the explicit control of backup compression settings, so for ad hoc database backups you should remember to compress to save space. Additionally, in SQL Server 2022 (16.x), the introduction of the ALGORITHM option enables the selection of a compression algorithm for the operation, with the default being MS_XPRESS. This flexibility overrides the server-level default settings, allowing users to tailor compression preferences for each backup.

COPY_ONLY
A “copy-only” backup, when specified, creates an independent snapshot of a database without disrupting the regular backup sequence. It is often employed for special purposes, like backing up the log before an online file restore, and is typically used once and then deleted.

STATS = 4
Displaying statistics during the backup operation provides real-time insights, offering transparency into the progress of the backup process. We can set any number, I choose 4 percent. Check out my other post to help get estimated backup times using a SQL script if you’re working with a large database.

Adding a Transaction Log (TLOG) Backup

Beyond Full Backups, SQL Server provides the ability to perform Transaction Log (TLOG) backups, crucial for maintaining a comprehensive backup strategy.

Performing a TLog Backup

-- Execute Transaction Log Backup with Date Timestamp
DECLARE @timestamp NVARCHAR(20) = REPLACE(CONVERT(NVARCHAR, GETDATE(), 120), ':', '');
DECLARE @backupPath NVARCHAR(255) = 'D:\mssql_backups\Jupiter_TLOGBackup_' + @timestamp + '.trn';

BACKUP LOG Jupiter
TO DISK = @backupPath
WITH 
    COMPRESSION,                    -- Use compression
    STATS = 4;                      -- Display statistics during the backup operation

Key Considerations for TLOG Backups:

Transaction Log Backups: Capture and store transaction log information, allowing point-in-time recovery.
Frequent Execution: Execute TLOG backups frequently to minimize data loss in case of a failure.
Backup Chain: A Full Backup must precede the first TLOG backup to establish the backup chain.

By incorporating Transaction Log backups into your strategy, you enhance data protection and pave the way for efficient recovery in critical scenarios.