Quick answer
Create a SQL Server backup with one command — BACKUP DATABASE YourDB TO DISK = 'C:\Backups\YourDB.bak' WITH COMPRESSION, CHECKSUM — or in SSMS: right-click the database, Tasks, Back Up. Full, differential and log backups each have a role; verify every backup with RESTORE VERIFYONLY, because an unverified backup is a hope.
Backing up SQL Server is one of those tasks that looks optional right up until the disk fails. SQL Server ships with everything needed for proper backups: three backup types, compression, checksum verification, and scheduling through Maintenance Plans or Agent jobs. No paid tool is required to create, secure, or restore a backup — this guide sticks to what the engine gives you.
The steps below were checked against SQL Server 2022 and current Microsoft documentation, October 2026. They apply equally to SQL Server 2016 onward, with minor menu differences in older SSMS versions.
What you need
- SSMS (SQL Server Management Studio) installed, or the ability to run sqlcmd.
- A service account with permission to write to the target folder.
- Enough free disk space — a full backup is roughly the size of the data in use, not the data file's allocated size (free space is skipped).
- A backup folder that is NOT inside the SQL Server data directory and is NOT NTFS-compressed.
Know the three backup types first
| Type | What it captures | Cadence it fits |
|---|---|---|
| Full | The entire database at that moment | Weekly as the anchor |
| Differential | Everything changed since the last full backup | Daily |
| Transaction log | All log records since the last log backup | Every 5-15 minutes for low RPO |
Differentials and log backups only make sense on top of a full backup; log backups additionally require the database to be in FULL (or BULK_LOGGED) recovery model. If your databases sit in SIMPLE recovery mode, the log cannot be backed up — the log truncates at each checkpoint, which is convenient for dev boxes and unacceptable for production systems where losing 15 minutes of data matters.
Method 1: T-SQL, the way that scales
The core command:
BACKUP DATABASE [YourDatabase]
TO DISK = 'E:\Backups\YourDatabase_Full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;
Read that as: write a compressed full backup to a named file, calculate page checksums during the backup, and report progress every 5 percent. Three details worth insisting on:
- CHECKSUM detects corruption during the backup instead of discovering it during a restore.
- COMPRESSION typically shrinks the .bak by half or more and offloads work from the disk.
- Square brackets around names avoid parse errors on databases with spaces or reserved words.
A differential backup is the same statement with DIFFERENTIAL added:
BACKUP DATABASE [YourDatabase]
TO DISK = 'E:\Backups\YourDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
And a log backup (FULL recovery model only):
BACKUP LOG [YourDatabase]
TO DISK = 'E:\Backups\YourDatabase_Log.trn'
WITH COMPRESSION, CHECKSUM;
One quirk from experience: naming log backup files with a .trn extension instead of .bak saves real confusion later, because a folder full of .bak files tells you nothing about which is the log chain.
To split one backup across several files (useful when a single disk is too small): repeat TO DISK clauses — DISK = 'E:\Backups\part1.bak', DISK = 'F:\Backups\part2.bak' — but note every file in the set is required for a restore.
Method 2: SSMS, the visual way
1. Open SSMS, connect to the instance, expand Databases.
2. Right-click the database, choose Tasks, then Back Up.
3. Under Backup type, pick Full (or Differential; Log appears only in FULL recovery model).
4. Under Destination, remove any stale default path you do not want, click Add, and enter the .bak file path.
5. Under Media Options, leave "Back up to a new media set" for a first backup; for routine backups keep "Append to the existing backup set" so the file holds history, or "Overwrite" for single-use files.
6. In Advanced: set "Verify backup when finished" (runs the equivalent of RESTORE VERIFYONLY), enable "Perform checksum before writing to media", and consider "Compress backup" if not defaulted server-side.
7. Click OK and watch the progress dialog; a multi-GB database can take minutes.
For scheduling the same job nightly, right-click SQL Server Agent, add a new job with a T-SQL step (Method 1's command), and a daily schedule — or use Maintenance Plans for a wizard-driven equivalent.
Verify the backup, then trust it
The restore is the only test that counts, but two cheap checks catch most disasters early:
RESTORE VERIFYONLY
FROM DISK = 'E:\Backups\YourDatabase_Full.bak'
WITH CHECKSUM;
VERIFYONLY walks the backup structure and confirms it is readable and complete — it does not prove the database restores cleanly on another server, but it catches truncated, corrupt or wrongly-named files in seconds. Quarterly, do the real thing: restore a recent backup to a test instance and run DBCC CHECKDB on it. The first time you do this, budget extra time for the surprises (orphaned logins, missing drive letters); that time is the actual price of a working recovery plan.
Securing the backups you just made
A backup is a copy of your most sensitive data, and it inherits every risk:
- Restrict folder access with NTFS ACLs so only the SQL Server service account and backup admins can read the .bak files.
- Encrypt or compress-then-store offsite: SQL Server 2014+ supports native backup encryption with a certificate or asymmetric key; without it, protect the folder with BitLocker/EFS and move copies offsite or to cloud storage on a schedule.
- Know the password options are weak: BACKUP's PASSWORD and MEDIASET PASSWORD options deter casual reads but are not encryption and are not supported by some restore tooling. Native encryption is the real answer.
- Mind the log chain: in FULL recovery, if a log backup goes missing or corrupt, point-in-time recovery past that point breaks. Ship log backups offsite as they are created.
- Never restore casually from untrusted sources: a database file from outside can carry malicious code in stored procedures; run DBCC CHECKDB and review the code on an isolated instance first.
Still not working?
- Backup fails with operating system error 3 (path not found): the folder does not exist from the service account's perspective, or the path is local to a different node in an availability group.
- Operating system error 5 (access denied): the SQL Server service account lacks write permission on the backup folder — grant it, or point at a folder it owns.
- Backup succeeds but RESTORE fails on another server: version mismatch (newer backup onto older SQL Server never works) or a missing split-file part.
- Database will not start after a crash and there is no valid backup: that is a recovery scenario, not a backup one — see our walkthrough of SQL Server error 3417 (master database recovery), and if backups exist but are corrupt, specialist recovery services can sometimes read damaged .bak files; treat any "repair tool" download with the usual skepticism until scanned and verified.
For the database behind a website, our guide to fixing the error establishing a database connection in WordPress covers the most common failure the public ever sees, and for moving whole databases between servers, our SQL Server to SQL Server migration guide covers attach-and-restore options.
FAQ
What is the difference between full, differential and log backups?
A full backup captures the entire database. A differential captures everything changed since the last full backup, so it grows through the week. Log backups capture the transaction log since the previous log backup, enabling point-in-time recovery down to seconds. A common schedule: weekly full, daily differential, log backups every 5-15 minutes.
How do I schedule automatic backups in SQL Server?
Use SQL Server Agent: create a job whose step runs the BACKUP DATABASE command, then add a daily schedule. Maintenance Plans offer the same in wizard form. On SQL Server Express, which lacks Agent, use Windows Task Scheduler running a sqlcmd script.
Where are SQL Server backups stored by default?
In the instance's BACKUP folder, typically C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance>\MSSQL\Backup — which sits on the same disk as the data, making it a poor default. Always target a separate disk or network location for real backups.
Can I back up a SQL Server database while it is in use?
Yes. Online backups are standard: SQL Server takes the backup from a consistent point in the transaction log without taking the database offline. Heavy I/O during the backup can affect performance, which is one reason backups are usually scheduled off-peak.
How do I check if a SQL Server backup is valid?
Run RESTORE VERIFYONLY FROM DISK = 'path' WITH CHECKSUM — it confirms the file structure is readable and complete without performing a restore. For full confidence, restore the backup to a test instance quarterly and run DBCC CHECKDB on it.
Why is my SQL Server backup so large?
Likely causes: compression is not enabled (add WITH COMPRESSION), the database has heavy index fragmentation or dead space, or you are backing up a database whose data files were never shrunk after a large delete. Compression typically halves the .bak; rebuilding fragmented indexes helps the rest.
