SQL BACKUP DB

What is the SQL BACKUP DATABASE command?

Creates a binary copy of a database so it can be restored after failure, corruption, or migration.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BACKUP DB: • Microsoft SQL Server (all editions) • Azure SQL Managed Instance • SQL Server on Linux • SAP ASE / Sybase (similar syntax) Not supported natively in MySQL, PostgreSQL, Oracle, or SQLite.

SQL BACKUP DB Full Explanation

BACKUP DATABASE (often shortened in conversation to BACKUP DB) is a data-protection command that writes all pages of the specified database, plus transaction-log information needed for a consistent restore, to one or more backup devices. In SQL Server it is the foundation of disaster-recovery planning, allowing full, differential, and copy-only backups. The statement runs online, meaning the database remains readable and writable during the operation. Backups can be written to disk files, tapes, URL endpoints (Azure storage), or logical devices. Options control compression, checksum validation, encryption, stats output, and whether the backup is full, differential, or copy-only. BACKUP DATABASE does not truncate the transaction log (that is handled by BACKUP LOG) and it does not verify restorability until a RESTORE VERIFYONLY is run. Adequate permissions (sysadmin or db_owner plus BACKUP DATABASE privilege) are required. Although other platforms support backup utilities, the BACKUP DATABASE T-SQL command is specific to SQL Server and Sybase-derived engines.

SQL BACKUP DB Syntax

BACKUP DATABASE database_name
TO DISK = 'C:\Backups\database_name_yyyy_mm_dd.bak'
[ , ... additional devices ]
[WITH
    DIFFERENTIAL | COPY_ONLY,
    COMPRESSION,
    DESCRIPTION = 'Free-text note',
    NAME = 'LogicalBackupName',
    CHECKSUM,
    STATS = 10,
    ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert ),
    FORMAT,
    INIT
];

SQL BACKUP DB Parameters

database_name (sysname) - Name of the database to back up
TO (clause) - One or more backup devices (DISK, TAPE, or URL) and their paths
DIFFERENTIAL (keyword) - Backs up only changed extents since last full backup
COPY_ONLY (keyword) - Takes a full backup that does not reset the differential base
COMPRESSION (keyword) - Compresses the backup (Enterprise or later editions where supported)
DESCRIPTION (string) - Human-readable description stored in the backup header
NAME (string) - Logical name for the backup set
CHECKSUM (keyword) - Verifies page checksums while backing up and stores them in the file
STATS (int) - Percent-complete reporting interval
ENCRYPTION (clause) - Encrypts the backup using a certificate or asymmetric key
FORMAT, INIT (keyword) - Controls whether to overwrite or append to existing media

Example Queries Using SQL BACKUP DB

--Full backup to local disk
BACKUP DATABASE Sales
TO DISK = 'D:\SQLBackups\Sales_full_2024_04_15.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

--Differential backup
BACKUP DATABASE Sales
TO DISK = 'D:\SQLBackups\Sales_diff_2024_04_16.bak'
WITH DIFFERENTIAL, COMPRESSION;

--Copy-only backup to Azure Blob Storage
BACKUP DATABASE Sales
TO  URL = 'https://mystorage.blob.core.windows.net/sqlbackups/Sales_copyonly.bak'
WITH COPY_ONLY, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = SalesBackupCert);

Expected Output Using SQL BACKUP DB

A
BAK file (or stripe set) is created at the specified location
The backup file contains the database pages, metadata, and necessary transaction-log records
Progress is printed when STATS is used
Upon success SQL Server writes a message to the error log and returns "BACKUP DATABASE successfully processed"

Use Cases with SQL BACKUP DB

Nightly full backups for disaster recovery
Hourly differential backups to reduce recovery-point-objective
On-demand copy-only backup before a risky deployment or migration
Compressed backups to save storage and speed up transfers
Encrypted backups for compliance when storing off-site or in cloud storage

Common Mistakes with SQL BACKUP DB

Forgetting COPY_ONLY on an ad-hoc backup, which breaks the differential chain
Assuming BACKUP DATABASE truncates the log (it does not)
Running FORMAT unintentionally and erasing older backup sets on the same media
Omitting CHECKSUM and discovering corruption only during restore
Backing up to a local disk on the same server, which offers no disaster protection

First Introduced In

Microsoft SQL Server 7.0 (1998)

Frequently Asked Questions

What permissions are needed to run BACKUP DATABASE?

Members of the sysadmin fixed server role, the db_owner database role, or users granted the BACKUP DATABASE permission can execute the command.

Does BACKUP DATABASE shrink the transaction log?

No. Only BACKUP LOG (or switching the recovery model to SIMPLE) truncates inactive log records.

Can I back up to multiple files at once?

Yes. Specify additional TO DISK clauses to create a striped backup, improving throughput.

How do I verify a backup without restoring it?

Run RESTORE VERIFYONLY FROM DISK = 'path_to_backup.bak';