12. Backups and Restores

• SIMPLE: Low overhead, RPO is last backup. Log truncation is done at CHECKPOINT.
• FULL: Log truncated after log backup and CHECKPOINT. Allows PITR.
• BULK LOGGED: Used for bulk loading. Minimal logging. Best to do a full backup afterwards and after returning to FULL recovery mode.

ALTER DATABASE db SET RECOVERY SIMPLE|FULL|BULK_LOGGED;

Full backup in any recovery model does:

• CHECKPOINT
• data read phase: every page is backed up
• log read phase: enough log for a consistent recovery
• RPO is at end of backup

Differential backup:

• Only in FULL or BULK_LOGGED
• Not SIMPLE
• FULL Only transaction logs
• BULK_LOGGED Transaction logs plus modified pages that include minimally logged transactions
• Backup means VLFs are truncated until first active VLF is found
• Allow PITR
• Least resource intensive

TAPE backup deprecated

Backup device

• physical file on disk
• tape
• Azure BLOB

Media

• maximum of 64 backup devices
• can be mirrored/stripped within a media set
• Media family
• maximum of 4 mirrors for a device
• media set must be of same type, DISK or TAPE

Logical Backup Device sp_adddumpdevice

Media Set: Each media family has:

• family sequence number
• physical sequence number

Backup Set - append/overwrite media

Backup Strategies

• FULL DATABASE: Master, MBSDB. RPO is time of last backup
• FULL plus TRANSACTION LOG backup: RPO is frequency of transaction log backup. RTO determines frequency of FULL backup.
• FULL, DIFFERENTIAL, TRANSACTION LOG backup: Differential is cumulative
• FILEGROUP Backup
  • need only restore corrupt filegroup
  • Backup filegroup instead of files as tables are stored across multiple files in filegroups
• Partial Backup - only read/write filegroup not read-only filegroup

Copy only backup is not entered into restore sequence. Does not reset for differential backup. No copy - only differential backup.

Can do copy-only transaction logs backup:

• log no truncated
• does not affect log archive point

‘Perform Checksum Before Writing to Media’ option chosen when doing backup more often than DBCC CHECK DATABASE.

BACKUP DATABASE db
  TO DISK='fn'
  WITH
    RETAINDAYS=90,
    FORMAT,
    INIT,
    MEDIANAME='...',
    NAME='...',
    COMPRESSION;

WITH DIFFERENTIAL ...

BACKUP LOG db
  TO DISK='fn'
  WITH
    RETAINDAYS=90,
    NOINIT,
    MEDIANAME='...',
    NAME='...',
    COMPRESSION;

/* INIT overwrites existing backup file */
/* FORMAT overwrites media headers */

BACKUP DATABASE db
  FILEGROUP='fg' ...

Restoring a Database

RESTORE WITH VERIFY ONLY

• backup set is complex
• all backup device readable
• CHECKSUM valid
• page headers verified
• enough space for restore

Restore with restricted access:

• administrators
• db_creator

• db_owner

• RECOVERY - database online after restore completes
• NORECOVERY - database left in restoring state
• STANDBY
  • database online but read-only
  • failover to secondary server

WITH PARTIAL allows apply of additional backup even using RECOVERY. PIECEMEAL recovery. Tail log backup.

PITR:

• sys.fn_dump_dblogs()
• MSDB.dbo.suspect_pages

RESTORE DATABASE db
  PAGE='n:m'
  FROM DISK=N'...'
  WITH
    FILE=1,
    NORECOVERY,
    STATS=5;
BACKUP LOG db;
RESTORE LOG db;

Piecemeal Restores

Use filegroup to backup and restore.