SQL BACKUP DATABASE

What is SQL BACKUP DATABASE?

Creates a physical backup of a SQL Server database to disk, tape, or URL.

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 DATABASE: Supported: Microsoft SQL Server (all editions), Azure SQL Managed Instance. Not supported: PostgreSQL, MySQL, MariaDB, Oracle, SQLite, Snowflake.

SQL BACKUP DATABASE Full Explanation

BACKUP DATABASE is a data-protection statement available in Microsoft SQL Server that copies the entire database (or its differential changes) into a backup device such as a disk file, tape drive, or cloud storage URL. The backup captures the database at a transaction-consistent point in time, enabling later recovery with RESTORE DATABASE. You can run full, differential, or copy-only backups, mirror them to multiple locations, compress them, and verify them with CHECKSUM. BACKUP DATABASE can run while the database is online, using internal mechanisms to ensure data consistency. It requires appropriate permissions (typically membership in the sysadmin or db_backupoperator roles). A backup set’s reliability depends on regularly testing restores and storing copies off-site. The keyword is specific to SQL Server; other systems use external tools rather than an in-engine statement.

SQL BACKUP DATABASE Syntax

BACKUP DATABASE database_name
TO <backup_device> [ ,...n ]
[ MIRROR TO <mirror_device> [ ,...n ] ]
[ WITH
    [ DIFFERENTIAL ]
    [ COPY_ONLY ]
    [ FORMAT | NOFORMAT ]
    [ INIT | NOINIT ]
    [ NAME = 'backup_set_name' ]
    [ COMPRESSION | NO_COMPRESSION ]
    [ CHECKSUM | NO_CHECKSUM ]
    [ CONTINUE_AFTER_ERROR ]
    [ STATS = percentage ]
] ;

SQL BACKUP DATABASE Parameters

database_name (sysname) - The database to back up.
MIRROR TO - Optional second, third, or fourth backup device for identical copies.
DIFFERENTIAL - Stores only changes since last full backup.
COPY_ONLY - Creates a backup that does not affect the differential base.
FORMAT / NOFORMAT - Overwrite or preserve existing media headers.
INIT / NOINIT - Overwrite or append within the media set.
NAME - Human-readable label for the backup set.
COMPRESSION - Compresses the backup (default depends on server setting).
CHECKSUM - Adds page checksums to validate on restore.
STATS - Reports progress every n percent.

Example Queries Using SQL BACKUP DATABASE

-- Full backup to local disk
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_full_2024_04_01.bak'
WITH COMPRESSION, STATS = 10;

-- Differential backup
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\Backups\AdventureWorks_diff_2024_04_02.bak'
WITH DIFFERENTIAL, NAME = 'AdventureWorks diff 2024-04-02';

-- Copy-only backup to Azure Blob
BACKUP DATABASE Sales
TO URL = 'https://myacct.blob.core.windows.net/sqlbackups/sales_copyonly.bak'
WITH COPY_ONLY, CHECKSUM;

-- Mirrored backup to two locations
BACKUP DATABASE HR
TO DISK = 'E:\HR_full.bak'
MIRROR TO DISK = 'F:\Mirror\HR_full.bak'
WITH INIT, STATS = 5;

Expected Output Using SQL BACKUP DATABASE

A
bak file (or multiple files) is created at the specified location containing the database backup
SQL Server prints progress messages and a final message like "BACKUP DATABASE successfully processed X pages in Y seconds
"

Use Cases with SQL BACKUP DATABASE

Regular full and differential backups as part of a maintenance plan
Ad-hoc copy-only backup before a risky deployment
Creating a restorable snapshot for staging or QA environments
Mirroring backups to two disks for immediate redundancy
Backing up directly to cloud storage to satisfy off-site requirements

Common Mistakes with SQL BACKUP DATABASE

Forgetting WITH INIT and unintentionally appending to an old media set
Relying only on full backups and skipping log or differential backups, increasing recovery time
Not testing restores and assuming backups are valid
Omitting COPY_ONLY during a one-off backup, which disrupts the differential chain
Running backups to the same physical disk as the database, losing redundancy

First Introduced In

Microsoft SQL Server 6.5

Frequently Asked Questions

What permission is required to run BACKUP DATABASE?

Members of the sysadmin fixed server role and the db_owner or db_backupoperator roles within the database can back up that database.

Can I back up to multiple files in parallel?

Yes. Specify multiple DISK clauses separated by commas or use MIRROR TO for identical copies. SQL Server stripes pages across the files, improving throughput.

How can I verify that a backup is usable?

Use RESTORE VERIFYONLY to check header and checksum integrity, or restore the backup to a non-production environment and run DBCC CHECKDB.

Is compression always recommended?

Compression reduces storage and network transfer size and is generally recommended, but it increases CPU usage. Benchmark on your workload before enabling by default.