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.

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.