How to back up a SQL Server database

You can back up a SQL Server database from SQL Server Management Studio (SSMS) in a few clicks, or with one T-SQL statement you can script and schedule. This guide covers both, plus the options that decide whether the backup is useful when you need it.

Before you start: check the recovery model

The recovery model decides which backups you can take and how much data you can lose.

SELECT name, recovery_model_desc FROM sys.databases ORDER BY name;

A database in FULL recovery must have regular log backups. Otherwise the log file keeps growing until the disk fills up.

Back up a database in SSMS

1. In Object Explorer, expand Databases, right-click the database and choose Tasks > Back Up.
2. Set Backup type to Full.
3. Under Destination, remove any old path, click Add and enter a full file path such as D:\SQLBackups\Sales_FULL_20260927.bak. The path is on the server, not on your own PC.
4. On Media Options, tick Verify backup when finished and Perform checksum before writing to media.
5. On Backup Options, set Compression to Compress backup if your edition supports it (Standard and Enterprise do, Express does not).
6. Click OK. When it finishes, check the file exists and has a sensible size.

Back up with T-SQL

Full backup

BACKUP DATABASE [Sales]
TO DISK = N'D:\SQLBackups\Sales_FULL_20260927_0200.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;

Remove COMPRESSION on SQL Server Express.

Differential backup

Contains everything changed since the last full backup. It grows each day until the next full backup.

BACKUP DATABASE [Sales]
TO DISK = N'D:\SQLBackups\Sales_DIFF_20260928_0200.bak'
WITH DIFFERENTIAL, CHECKSUM, COMPRESSION, STATS = 10;

Transaction log backup (FULL recovery only)

BACKUP LOG [Sales]
TO DISK = N'D:\SQLBackups\Sales_LOG_20260928_1015.trn'
WITH CHECKSUM, COMPRESSION;

Log backups every 15 to 60 minutes are common. If this fails with error 4214, see fix error 4214.

One-off copies: use COPY_ONLY

Need a copy to give a developer or move to another server? Use COPY_ONLY. An ordinary full backup resets the base that your scheduled differential backups depend on. A copy-only backup leaves your backup chain alone.

BACKUP DATABASE [Sales]
TO DISK = N'D:\SQLBackups\Sales_COPY_20260927.bak'
WITH COPY_ONLY, CHECKSUM, STATS = 10;

Options worth using every time

• CHECKSUM validates page checksums while reading, so corruption is caught at backup time instead of restore time.
• COMPRESSION usually cuts file size and backup time substantially, at some CPU cost.
• A unique file name per backup. Include the database, type and a timestamp. Appending many backups to one file makes restores and cleanup confusing.
• A separate disk. A backup on the same disk as the data files is lost with it.

Permissions

Two different accounts are involved. Your login needs db_backupoperator in the database (or sysadmin). The SQL Server service account needs write access to the destination folder, because the server process writes the file. Folder permission problems show up as error 3201 with operating system error 5.

Check which backups actually happened

SQL Server records every backup in msdb. This shows the last full backup for each database, so you can spot any that are not covered:

SELECT d.name,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name
ORDER BY last_full;

Next steps

A manual backup protects you today. To protect you every day, schedule the backup, verify it, and keep a copy off the server.