The most common situation we see in the field is this: backups are being taken, but nobody knows whether you can actually restore from them. The gap between those two sentences is as big as whether a company can keep operating or not.
Taking a backup is a task; being able to restore is a capability. An untested backup doesn’t count as a backup.
Answer two questions first: RPO and RTO
Before getting into technical detail, there are two numbers the business side needs to decide on:
- RPO (Recovery Point Objective): How much data loss can you accept in a disaster? 24 hours, 1 hour, 5 minutes?
- RTO (Recovery Time Objective): How long can it take, at most, to get the system running again? Half a day, 2 hours?
Any backup plan built without defining these two numbers is guesswork. A company taking a nightly backup once a day has an RPO of 24 hours — meaning an afternoon failure loses that day’s invoices, orders, and payments. Is that acceptable? That answer comes from management, not the technical team.
Types of SQL Server backups
Full backup: The entire database. The foundation of every restore.
Differential backup: Everything that changed since the last full backup. Shortens restore time and reduces backup size.
Transaction log backup: Only possible under the FULL recovery model. Lets you restore to a specific point in time (e.g., one minute before an accidental delete) and prevents the log file from growing indefinitely.
A typical setup looks like this: a full backup weekly, a differential backup daily, a log backup every 15 minutes. This combination brings RPO down to 15 minutes.
The recovery model trap
If your database is in FULL recovery model and you’re not taking log backups, the log file keeps growing until one day the disk fills up. This is a classic cause of incidents reported as “SQL crashed.”
The rule is simple: if you’re using the FULL model, you must take regular log backups. If you’re not, and you don’t need minute-level recovery, the SIMPLE model is the more sensible choice.
The 3-2-1 rule
A well-established, healthy backup distribution:
- 3 copies of the data
- 2 different media/storage types
- 1 copy offsite
One more item needs to be added to this today: 1 copy offline or immutable. Ransomware now encrypts or deletes any backup it can reach first. A backup sitting on a NAS on the same network can be lost along with the main server.
Backup verification and integrity checks
After taking a backup:
RESTORE VERIFYONLY FROM DISK = 'D:\Backup\ERP_full.bak';This command confirms the backup is readable, but it doesn’t guarantee the integrity of its contents. That’s why you should also regularly run an integrity check on the database itself:
DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS;Backing up a corrupt database just carries the corruption into the backup. If the problem is only discovered months later, every backup you have may already be unusable.
The restore test: the step most often skipped
At least once a quarter, you should attempt a real restore from a backup onto a separate server, and record:
- How long did the restore take? (That’s your actual RTO.)
- Which steps caused problems?
- Did the application work correctly with the restored database?
- Are user accounts (login/user mapping), permissions, and jobs all in place?
A company that has never run this test doesn’t actually know its RTO — and finding out during a crisis is the most expensive way to learn it.
Common mistakes
- Keeping backups on the same disk as the database
- No notification going out when a backup job fails
- Unencrypted backups sitting in a shared folder
- System databases (master, msdb) not being backed up
- No retention policy ever defined, even though it matters as much as the backup itself
Bottom line
Backup isn’t a cost line item, it’s insurance — and like any insurance, its value only shows up on a bad day. Don’t just make sure your backups exist; make sure you can restore from them.
Put Your Backup Plan in Writing
If the backup routine exists only in one person’s head, the plan disappears when that person cannot be reached. A short, current document saves time in a crisis. It should contain at least:
- A list of the databases being backed up and the business owner of each
- The agreed RPO and RTO values for each database
- Backup types, schedules and storage locations
- Who receives notifications about failed backups
- Where certificates and keys are stored if backup encryption is used
- The restore steps and the people authorised to carry them out
You cannot restore an encrypted backup without its certificate. Keep the certificate in a separate, secure location, not alongside the backups.
The Restore Sequence During an Outage
In an outage, the first reaction is often to restore immediately. The right sequence, however, reduces data loss.
- Assess the situation and do not overwrite existing files. The damaged database may be needed for later investigation.
- If you use the FULL recovery model and the log file is accessible, take a final log backup (tail-log) first.
- Decide which point in time you want to return to. After a faulty operation, target the moment just before it.
- Restore the full backup, then the differential and the log backups in order. Bring the database online only at the last step.
- Run an integrity check, then test the application and user access.
To rehearse and document this sequence in advance, you can draw on SQL consulting support.
A Weekly Backup Check Routine
Backup jobs can fail silently. A short weekly check lets you notice problems before you need the backup. Assign the check to one person and record the result.
- Check the last backup date of every database in the backup history in msdb.
- Review jobs that failed or did not run at all.
- Monitor free space on the backup disk and its growth trend.
- Confirm that the offsite copy is actually being updated.
- Make sure newly added databases are included in the plan.
To go beyond backups and set up recovery for all your systems, read our guide on creating a disaster recovery plan.
Frequently Asked Questions
Can a virtual machine snapshot replace a SQL backup?
Not on its own. Snapshots are usually kept on the same infrastructure and are not designed for long-term retention. They also lack the flexibility of returning to a specific point in time, which log backups provide. It is sounder to treat snapshots as an additional layer that complements SQL Server’s own backups.
Should we use backup compression?
Compression reduces the size of the backup file and, because less data is written to disk, often shortens the backup duration as well. In return, processor usage rises during the backup. For backups that run during busy hours, monitor this effect and check whether your edition supports the feature.
How long should we keep backups?
There is no single correct answer. The retention period depends on your business needs and the legal obligations that apply to you. Also consider how late a data error might be noticed. We recommend reviewing your obligations with your financial and legal advisers and defining the period as a written policy.
What is a COPY_ONLY backup and when is it used?
COPY_ONLY is an independent backup taken without affecting the existing backup chain. It is used in unplanned situations, such as moving data to a test environment or taking an extra copy before an update. A normal full backup changes the base that later differential backups rely on, whereas COPY_ONLY leaves that sequence intact.
At ÇAP Teknoloji, we help design, automate, and monitor backup strategies, along with running regular restore tests. If you’d like to find out whether your current backup setup actually works, you can learn more about our SQL consulting service or get in touch with us.