The .bak file on the same server: what it really protects you from

Open almost any small-business SQL Server in Pakistan and you will find a maintenance plan writing a .bak file to another drive on the same machine, every night, for years. It is a reasonable first step and it is better than nothing. It is also protecting against exactly one thing.

The one thing it covers

Somebody deletes rows, or a bad update runs, and you need last night. The .bak file is right there and the restore is quick. Genuinely useful — this is the most common kind of accident.

Everything it does not cover

• The machine. Motherboard, power supply, RAID controller — when the server goes, the backup goes with it.
• Ransomware. A .bak file is a file. It is on a local drive, so it is encrypted along with everything else, and it is a particularly attractive target.
• The building. Fire, theft, flood, and a surge severe enough to take both drives.
• Time between backups. A nightly full backup at 2 am means an afternoon failure costs a full day of entries. For an accounting or inventory system, a day of re-entry is not a small thing.

Two changes that fix most of it

Get a copy off the machine, automatically. Not a person copying it to a USB stick on Fridays. Automatically, every night, to somewhere the server itself cannot browse to.

Add transaction-log backups. Full backups nightly, logs every fifteen or thirty minutes. This changes recovery from "yesterday at 2 am" to "the moment before the problem", and it is usually a configuration change rather than a purchase.

Then test it

Restore last night's backup to a spare instance and open it. Not eventually — this month. A backup file that has never been restored is a file whose only claim to being a backup is its extension.

Most businesses that lose a database were doing something. It just turned out to be the one thing that did not cover the day they had.