SQL Server Backup Safety: 10 Rules to Protect Backups from Ransomware and Human Error

Taking backups is only half of protecting data. The other half is that the backup is actually there, intact and restorable on the day you need it. Ransomware now targets not just databases but the backup folders on the same network, and a DROP DATABASE run on the wrong server or a DELETE missing its WHERE clause are still among the most common disaster scenarios.

This guide isn’t about which backup types to take and how often (our backup plan guide covers that). It’s about protecting the backups you take: ten rules against attacks, human error and unnoticed corruption.

1. Start from the Targets: RPO and RTO

For each database, answer two questions with the business: how much data loss is acceptable at most (RPO), and how long can the system be down at most (RTO)? A five-minute RPO means log backups at least every five minutes and the FULL recovery model. A two-hour RTO means the backup must sit somewhere, and be fast enough, to restore within two hours. Every other backup safety decision follows from these two targets.

2. The 3-2-1 Rule and Beyond

The classic rule: at least 3 copies of the data, on 2 different media, with at least 1 off-site. In the ransomware era, two more items belong on the list: at least one copy must be immutable or offline, and restore tests must complete with zero errors.

3. Restrict Access to the Backup Target

• Only the SQL Server service account should be able to write to the backup share; domain admins and application accounts shouldn’t.
• The second tier that keeps backups long-term (backup server, object storage, tape) should be managed with credentials separate from the production domain.
• In object storage (for example Azure Blob Storage), immutability policies prevent backups from being deleted or modified for a set period. SQL Server can write directly to such targets with BACKUP TO URL.

4. Encrypt Backups and Store the Keys Separately

A backup file is a portable copy of the entire database. Anyone who gets hold of an unencrypted backup can restore it on another server and read the data. From SQL Server 2014, backups can be encrypted directly:

-- Once: create a certificate in master (it needs a database master key in master)
USE master;
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<strong password>';
CREATE CERTIFICATE BackupCert WITH SUBJECT = N'Backup encryption';

-- Back up the certificate with its private key right away
BACKUP CERTIFICATE BackupCert TO FILE = N'D:\Keys\BackupCert.cer'
WITH PRIVATE KEY (FILE = N'D:\Keys\BackupCert.pvk',
                  ENCRYPTION BY PASSWORD = N'<strong password>');

-- Encrypted, compressed backup with checksum
BACKUP DATABASE SalesDB TO DISK = N'\\backup\sql\SalesDB_full.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert),
     COMPRESSION, CHECKSUM;

5. Use CHECKSUM on Every Backup

WITH CHECKSUM verifies page checksums during the backup and generates a checksum for the backup itself. If a page is corrupt, the backup fails instead of silently copying the corruption. To make it the server-wide default:

EXEC sys.sp_configure N'backup checksum default', 1;
EXEC sys.sp_configure N'backup compression default', 1;
RECONFIGURE;

Our backup compression guide covers the effect of compression on CPU and duration.

6. Monitor Backup Status

A backup job that fails silently is more dangerous than no backup at all, because everyone assumes the backup was taken. Check the latest backups together with their checksum and encryption status:

SELECT d.name AS database_name, d.recovery_model_desc,
       b.type, b.backup_finish_date,
       b.has_backup_checksums, b.is_copy_only,
       b.encryptor_type, b.compressed_backup_size / 1048576 AS size_mb
FROM sys.databases AS d
OUTER APPLY (SELECT TOP (1) *
             FROM msdb.dbo.backupset AS bs
             WHERE bs.database_name = d.name AND bs.type = 'D'
             ORDER BY bs.backup_finish_date DESC) AS b
WHERE d.database_id <> 2
ORDER BY b.backup_finish_date;

Set up notifications for failed backup jobs, and a separate alert for “database without a backup in the last 24 hours”.

7. Restore Tests: The Only Real Proof

RESTORE VERIFYONLY checks that the backup file is readable and structurally complete, but it doesn’t prove that the data inside is usable. The only real proof is restoring the backup on a separate server and running DBCC CHECKDB on it.

• Restore one of the critical databases regularly, at least once a month.
• Record how long the test takes; that duration is your real RTO.
• Test point-in-time restore too: it’s the only way to see that the full, differential and log chain is complete.

Our restore guide covers the restore steps in detail.

8. COPY_ONLY for Ad Hoc Backups

An ordinary full backup that a developer takes to copy data to a test environment changes the base of the differential chain. If that backup file is later deleted, the subsequent differential backups become useless. Make it a rule that unplanned backups are always taken WITH COPY_ONLY.

9. Guard Against Human Error

• Register production and test servers in SSMS with different colors and clear names; most commands run on the wrong server come from this confusion.
• Take a log backup in production before DROP, large DELETE and UPDATE operations; a recovery point is then minutes away.
• Minimize standing permissions; decide deliberately who can delete what in production.

10. Document It

Where the backups live, how they’re encrypted, where the certificates are kept, retention periods and a step-by-step restore procedure belong in one document. Having to search for this information during a crisis is RTO’s worst enemy.

Backup Safety Checklist

• RPO and RTO are defined for each database; backup frequency matches them.
• 3-2-1 is in place; at least one copy is immutable or offline.
• Only the service account can write to the backup target.
• Backups are encrypted; the certificate and its password are stored separately and their restore is tested.
• All backups are taken WITH CHECKSUM.
• Alerts exist for failed and missing backups.
• A monthly restore and CHECKDB test runs, and its duration is recorded.
• Unplanned backups are taken COPY_ONLY.