Quick answer:
- Pick a recovery model based on acceptable data loss: Simple if losing up to a day is acceptable, Full if you need point-in-time restore.
- Common schedule: daily full (or weekly full + daily differential), plus transaction log backups every 15–60 minutes under the Full model.
- Keep at least one copy off the server (offsite NAS or cloud) and test restores regularly; an untested backup may not work.
Many accounting, ERP and sales systems store their data in Microsoft SQL Server. When a disk fails, ransomware hits or someone deletes records by mistake, the database backup is the only way back to work. This guide shows how to automate SQL Server backups in a way a small or mid-sized business can actually maintain.
Step 1: Define RPO and RTO
- RPO (how much data you can lose): accounting often accepts one day; sales and inventory may accept only 15–30 minutes.
- RTO (how fast you must be back): for example, within 4 business hours.
These two numbers drive the recovery model, backup frequency and storage location.
Step 2: Choose a recovery model and backup types
| Backup type | Contents | When to use |
|---|---|---|
| Full | The whole database | The base of every plan, at least weekly |
| Differential | Changes since the last full | Large databases needing faster daily backups |
| Transaction log | Log records since the last log backup | Full recovery model only; enables point-in-time restore |
Note: a database in Full recovery without regular log backups will see its log file grow continuously and can fill the disk.
Step 3: Automate the schedule
| Data profile | Suggested schedule |
|---|---|
| Under 10 GB, one day of loss acceptable | Daily full after hours, Simple recovery |
| 10–100 GB, 1-hour RPO | Sunday full, nightly differential, log every 60 minutes |
| Continuous transactions (sales, inventory) | Nightly full, log every 15 minutes |
SQL Server Standard/Enterprise use SQL Server Agent and Maintenance Plans for scheduling. SQL Server Express has no SQL Server Agent, so use Windows Task Scheduler to run a sqlcmd or PowerShell script. This is a common gap: the accounting software gets installed and nobody schedules backups.
Use WITH CHECKSUM to detect damaged pages, and compress backups where your edition supports it.
Step 4: Retain copies with the 3-2-1 rule
- 3 copies of data (production + 2 backups).
- 2 different media (server disk and NAS).
- 1 copy offsite (branch NAS or cloud), ideally immutable to resist ransomware.
Suggested retention: daily backups for 14–30 days, month-end backups for 12 months, year-end backups per your accounting record-keeping requirements.
Step 5: Monitor and test restores
- Get a morning report of which jobs succeeded or failed and their size.
- Monthly, restore a backup to a test machine, run
DBCC CHECKDBand open the application to check figures. - Record the actual restore time and compare it with your RTO.
RESTORE VERIFYONLY only confirms the file is readable; it does not replace a real restore.
Check your SQL Server version
SQL Server 2016 reached the end of extended support on July 14, 2026. If your database runs SQL Server 2016 or older, plan the upgrade together with your backup cleanup. See also the server maintenance checklist.
Centralized backup monitoring with ITS Manager
The Backup module in ITS Manager, developed by IT Systems, runs scheduled SQL and file backups and shows each job’s status on a dashboard, so owners can see the last successful backup without asking IT. For databases on VPS or cloud, see database backup and PITR for VPS and cloud servers.
FAQ
Can SQL Server Express back up automatically?
Yes, but not through SQL Server Agent, which Express does not include. Use Windows Task Scheduler with a sqlcmd or PowerShell script, or a third-party backup tool.
How often should SQL Server be backed up?
At least a daily full backup for accounting systems. If you cannot lose more than an hour of data, use the Full recovery model with log backups every 15–60 minutes.
Is copying the .mdf file a valid backup?
No. Copying .mdf/.ldf files while SQL Server is running usually produces an inconsistent copy. Use BACKUP DATABASE or a SQL Server-aware backup tool.
Need your SQL Server backups reviewed? Contact IT Systems’ server maintenance service to check schedules and run a restore test.




