Backup and restore

In one line: everyone takes backups, but few people practise the restores. A backup is only as good as the restore you’ve rehearsed, and the common mistakes (restoring over production, skipping the tail of the log, an ad hoc full backup) lose data you didn’t have to lose.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/backup-restore on GitHub.

What you’ll see

  • “Someone ran DELETE without a WHERE.”
  • Msg 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
  • A restore that comes back missing the last few minutes or hours of work.

Why it happens

  • Restoring the database itself to before an accident also throws away everything that happened after it. In the demo, that meant 500 orders.
  • Work since the last log backup exists only in the database’s log. Restore without backing it up first and it’s gone: 300 orders in the demo.
  • A differential is based on the most recent full backup, any full backup. Someone’s “quick copy before the release” becomes the base of every differential after it. If that copy has since been deleted, the differentials can’t be restored.

What to do

  • Restore to a copy and copy back. Restore a second database WITH STOPAT the moment before the accident, then copy the missing rows back (with IDENTITY_INSERT to keep their IDs). In the demo all 102,000 rows came back and the 500 later orders were kept. Point in time needs FULL recovery and an unbroken chain of log backups.
  • Take a tail-log backup before restoring over a damaged database: BACKUP LOG ... WITH NORECOVERY.
  • Use COPY_ONLY for every backup outside the schedule. It doesn’t become the differential base and doesn’t break the log chain.
  • Back up WITH CHECKSUM and WITH COMPRESSION. Verify with RESTORE VERIFYONLY ... WITH CHECKSUM.

What doesn’t help

  • Treating VERIFYONLY as proof. It proves the file is readable. Only a real restore proves the database comes back.
  • Restoring over production in a hurry. Restore alongside it, then decide.

Check your server

-- Last full, differential and log backup per database, and whether they carry checksums.
SELECT d.name,
       d.recovery_model_desc,
       LastFull = MAX(CASE WHEN b.type = 'D' AND b.is_copy_only = 0 THEN b.backup_finish_date END),
       LastDiff = MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END),
       LastLog  = MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END),
       FullsLast7Days = SUM(CASE WHEN b.type = 'D' AND b.is_copy_only = 0
                                            AND b.backup_finish_date > DATEADD(day, -7, GETDATE()) THEN 1 ELSE 0 END),
       AllWithChecksum = MIN(CAST(b.has_backup_checksums AS int))
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

A FULL-recovery database with no recent LastLog has a log that never truncates. More full backups in a week than your schedule takes points at an ad hoc one that broke the differential chain.

See it happen

The backup-restore demo runs four real restores and counts the rows that come back:

  1. point in time after a DELETE
  2. the tail of the log
  3. the differential that won’t restore
  4. compressed, checksummed backups

Cleanup removes every backup file and history row it created.

Related: why is my transaction log full? · Accelerated Database Recovery · data compression

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights