How to Back Up a SQL Server Database

Someone asks you for "a copy of the database as a .bak file." Good news: for SQL Server this is about as easy as database tasks get, because .bak is the native backup format. This is a quick reference you can bookmark and come back to.

The 30-second answer

BACKUP DATABASE [YourDB]
TO DISK = 'D:\Backups\YourDB.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

Run that in SQL Server Management Studio (SSMS) against your instance and you get a single .bak file that restores cleanly onto another SQL Server. Everything below is the detail behind that one command.

The three backup types

You will almost always want a full backup for handing someone a copy, but it is worth knowing the family:

• Full: the entire database in one file. This is what a client usually means by "a copy."
• Differential: only what changed since the last full backup. Smaller and faster, but useless without its parent full backup.
• Transaction log: the log records since the last log backup. Used for point-in-time recovery; requires the Full recovery model.

Method 1: SSMS (point and click)

• Right-click the database -> Tasks -> Back Up...
• Backup type: Full
• Under Destination, remove the default and Add your path, e.g. D:\Backups\YourDB.bak
• (Optional) Options page -> tick Compress backup
• Click OK

Method 2: T-SQL (scriptable, repeatable)

A plain full backup:

BACKUP DATABASE [YourDB]
TO DISK = 'D:\Backups\YourDB.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

What the options mean:

• FORMAT, INIT: overwrite/initialise the backup file rather than appending to it.
• COMPRESSION: a much smaller file (Standard edition and up).
• STATS = 10: prints progress every 10%.

The one you actually want for a handover: COPY_ONLY

If the server already runs scheduled backups, taking an ordinary full backup can quietly break the differential/log chain those jobs rely on. COPY_ONLY takes a normal, fully restorable copy without disturbing that chain:

BACKUP DATABASE [YourDB]
TO DISK = 'D:\Backups\YourDB_copy.bak'
WITH COPY_ONLY, COMPRESSION, STATS = 10;

Use this whenever you are grabbing an out-of-band copy for someone.

Verify before you hand it over

A backup you haven't checked is a rumour. Confirm the file is readable and complete:

RESTORE VERIFYONLY FROM DISK = 'D:\Backups\YourDB.bak';

Restoring it (so you know it works)

Simple restore onto the same or another instance:

RESTORE DATABASE [YourDB]
FROM DISK = 'D:\Backups\YourDB.bak'
WITH REPLACE, RECOVERY, STATS = 10;

If the target server's folder layout differs, remap the data and log files with MOVE:

RESTORE DATABASE [YourDB]
FROM DISK = 'D:\Backups\YourDB.bak'
WITH MOVE 'YourDB' TO 'E:\Data\YourDB.mdf',
MOVE 'YourDB_log' TO 'F:\Logs\YourDB_log.ldf',
REPLACE, RECOVERY, STATS = 10;

(To find the logical names; YourDB, YourDB_log; run: RESTORE FILELISTONLY FROM DISK = '...';.)

Gotchas worth knowing

• Permissions: the file is written by the SQL Server service account, not your Windows login. That account needs write access to the destination folder, and the folder must exist on a drive the SQL Server can see.
• Version direction: you can restore a backup onto the same or a higher SQL Server version; never a lower one. Check the client's version before promising a restore.
• Where it lands: the path is on the database server, so a local D:\Backups means the server's D drive, not your laptop.
• Big databases: add COMPRESSION, and for very large ones you can stripe across multiple files: TO DISK = 'a.bak', DISK = 'b.bak'.

Cheat sheet

-- Full backup
BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

-- Copy for handover (won't disturb scheduled backups)
BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB_copy.bak'
WITH COPY_ONLY, COMPRESSION, STATS = 10;

-- Verify
RESTORE VERIFYONLY FROM DISK = 'D:\Backups\YourDB.bak';

-- Inspect logical file names
RESTORE FILELISTONLY FROM DISK = 'D:\Backups\YourDB.bak';

-- Restore
RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB.bak'
WITH REPLACE, RECOVERY, STATS = 10;