SQL for .NET developers Intermediate

Backup and restore

Backups are what save you after a bad deploy, a deleted table or a failed disk. This lesson explains full, differential and log backups, recovery models, restoring to a point in time, testing restores, and what Azure SQL does for you automatically.

Point-in-time restore
  1. StartFull backupSunday: the whole database
  2. Step 2DifferentialLast night: changes since Sunday
  3. Step 3Log backupsEvery 15 minutes since last night
  4. Step 4STOPATStop just before the mistake
  5. EndCopy rows backFrom the restored copy to the live database

Backup types

  • Full: the whole database. The starting point for every restore.
  • Differential: everything changed since the last full backup. Faster to take, quick to restore on top of the full.
  • Transaction log: every change since the last log backup. Lets you restore to any moment, such as one minute before a bad DELETE.
  • Recovery model FULL is needed for log backups; SIMPLE keeps the log small but only allows restoring to the last full or differential backup.

Taking backups

A common plan: full weekly, differential daily, log every 15 minutes. Store copies off the server, and use CHECKSUM so damaged pages are caught when the backup is written.

SQL
BACKUP DATABASE Shop TO DISK = N'D:\Backups\Shop_full.bak'
    WITH COMPRESSION, CHECKSUM, INIT;

BACKUP DATABASE Shop TO DISK = N'D:\Backups\Shop_diff.bak'
    WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, INIT;

BACKUP LOG Shop TO DISK = N'D:\Backups\Shop_log_1015.trn'
    WITH COMPRESSION, CHECKSUM;

Restoring to a point in time

Restore the full and differential backups WITH NORECOVERY, then the log backups, stopping at the moment before the mistake.

SQL
RESTORE DATABASE Shop_Restored FROM DISK = N'D:\Backups\Shop_full.bak'
    WITH MOVE 'Shop' TO N'D:\Data\Shop_Restored.mdf',
         MOVE 'Shop_log' TO N'D:\Data\Shop_Restored.ldf', NORECOVERY;
RESTORE DATABASE Shop_Restored FROM DISK = N'D:\Backups\Shop_diff.bak' WITH NORECOVERY;
RESTORE LOG Shop_Restored FROM DISK = N'D:\Backups\Shop_log_1015.trn'
    WITH STOPAT = '2026-10-28T10:12:00', RECOVERY;

Good habits

  • A backup you have never restored is a hope, not a backup. Restore to a test server regularly.
  • Restore under a new name, copy the lost rows back, and keep the live database running.
  • Azure SQL Database takes full, differential and log backups automatically and supports point-in-time restore within the retention period (7 days by default, up to 35).
  • Before a risky deploy, take a copy-only full backup (WITH COPY_ONLY) so the regular chain is not disturbed.

Interview question

How would you recover from an accidental DELETE that happened 10 minutes ago?

With the FULL recovery model and log backups, restore the last full backup, the latest differential and the log backups under a new database name WITH NORECOVERY, and finish with STOPAT set just before the DELETE. Then copy the deleted rows back into the live database. In Azure SQL, use point-in-time restore to a new database and do the same.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.