# SQL BACKUP DATABASE: How to Back Up a Database

- URL: https://betteranalyst.com/sql/backup-database

SQL guide · Tables, views & databases (DDL)

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

- [SQL CREATE DATABASE](https://betteranalyst.com/sql/create-database)
- [SQL DROP DATABASE](https://betteranalyst.com/sql/drop-database)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL DROP TABLE](https://betteranalyst.com/sql/drop-table)
- [SQL TRUNCATE TABLE](https://betteranalyst.com/sql/truncate-table)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)