SQL guide · Tables, views & databases (DDL)

SQL BACKUP DATABASE: How to Back Up a Database

A database backup is a saved copy of your data that you can restore after a mistake, a failure or a bad deployment. The BACKUP DATABASE statement is the SQL Server command that creates one, writing the data to a disk file or other device. It is also the name people search for when they want to know how backups work in any SQL database, so this guide covers both the SQL Server syntax and the equivalent tools in other systems.

SQL Server supports several backup types. A full backup copies the entire database. A differential backup copies only what changed since the last full backup, which makes it smaller and faster. A transaction log backup captures the log so you can restore to a specific point in time. A common strategy combines a weekly full backup, a daily differential and frequent log backups, tuned to how much data loss your business can tolerate.

A backup is only useful if you can restore it, so test restores regularly and keep copies on separate storage, ideally off the server. Other databases do not use a BACKUP DATABASE statement. PostgreSQL relies on command-line tools such as pg_dump, MySQL on mysqldump, SQLite on a file copy or the online backup API, and Oracle on RMAN. The dialect notes below describe each approach.

Basic syntax

-- SQL Server
BACKUP DATABASE database_name
TO DISK = 'path\file.bak'
[WITH options];

-- Differential
BACKUP DATABASE database_name
TO DISK = 'path\file.bak'
WITH DIFFERENTIAL;

-- Transaction log
BACKUP LOG database_name
TO DISK = 'path\file.trn';

The path must be valid on the server, not on your own computer, and the SQL Server service account needs write access to it. Common options include INIT to overwrite an existing backup set, COMPRESSION to shrink the file, CHECKSUM to verify page integrity, and FORMAT to start a fresh media set.

SQL BACKUP DATABASE examples

Full backup

This writes a complete copy of the shop database to a single file. A full backup is the base for every other backup type, so you need one before differential or log backups can be restored.

BACKUP DATABASE shop
TO DISK = 'D:\backups\shop_full.bak';

Compressed and verified full backup

Adding COMPRESSION reduces file size and often speeds up the job, CHECKSUM validates pages as they are read, and INIT overwrites any existing backup in the file. A follow-up RESTORE VERIFYONLY checks that the file is readable.

BACKUP DATABASE shop
TO DISK = 'D:\backups\shop_full.bak'
WITH COMPRESSION, CHECKSUM, INIT;

RESTORE VERIFYONLY
FROM DISK = 'D:\backups\shop_full.bak';

Differential backup

A differential backup stores only the data pages that changed since the last full backup. It is smaller than a full backup and quicker to take, though restoring needs the full backup first and then the differential.

BACKUP DATABASE shop
TO DISK = 'D:\backups\shop_diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

Backups outside SQL Server

Other databases use external tools rather than a SQL statement. These shell commands show a PostgreSQL dump and a MySQL dump, each producing a file you can later reload. They are run in a terminal, not in a SQL client.

-- Run in a shell, not in SQL
pg_dump -U app_user -F c -f shop.dump shop

mysqldump -u app_user -p --single-transaction shop > shop.sql

Where SQL dialects differ

SQL Server: Uses BACKUP DATABASE and BACKUP LOG, and RESTORE DATABASE to recover. The database must be in the full or bulk-logged recovery model for log backups. WITH COPY_ONLY takes a backup without disturbing the regular backup chain.

PostgreSQL: There is no BACKUP DATABASE. Use pg_dump for a logical backup of one database and pg_dumpall for the whole cluster. For point-in-time recovery use pg_basebackup together with WAL archiving.

MySQL: There is no BACKUP DATABASE statement. Use mysqldump for logical backups, with --single-transaction for a consistent InnoDB snapshot, or tools such as MySQL Enterprise Backup and Percona XtraBackup for physical backups.

SQLite: A database is one file, so you can copy it when no writes are happening. For a live database use the .backup command in the sqlite3 shell, the online backup API, or VACUUM INTO (version 3.27 and later) to write a compact copy.

Oracle: Backups are normally taken with RMAN (Recovery Manager), for example BACKUP DATABASE; inside an RMAN session, or with Data Pump (expdp) for logical exports.

Common mistakes

Never testing a restore. A backup you have not restored is an assumption, so run restore tests on a schedule.
Storing the backup file on the same disk or server as the database, so one failure destroys both. Copy backups to separate storage.
Using a path that exists only on your own machine. In SQL Server the path is resolved on the server and must be writable by the service account.
Expecting a plain differential backup to work without a base full backup, or skipping log backups when you need point-in-time recovery.
Using a full backup of a busy database but ignoring consistency options. Use the right flags, such as --single-transaction in mysqldump, to get a consistent copy.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator