Skill 2.2 – Restore Databases

Design a Restore Strategy

In case of disaster:

  1. Do not panic
  2. Think about people, processes, and recovery assets
  3. Stop access to the DB
  4. See if there is a tail-log
  5. backup the tail-log (if you can)
  6. access damage / corruption
  7. Assess whether a restore strategy is required
  8. Assess whether point-in-time recovery is required
  9. Assess what backups exist
  10. Verify backups are current and valid
  11. Plan restore strategy
  12. Initiate restore process

RESTORE command takes some arguments:

  1. FILELISTONLY – list of database and log files contained in the backup
  2. HEADERONLY – returns backup header info for all backup sets
  3. LABELONLY – lists media information
  4. VERIFYONLY – verifies the backup, but doesn’t restore it
  5. MOVE – relocates the database’s files
  6. NORECOVERY -will not roll back uncommitted transactions
  7. RECOVERY – rolls back uncommitted transactions
  8. REPLACE – bypass internal safety check. Overwrites without mercy
  9. STANDBY – creates a second transaction log.  allows database to be read-only so you can inspect between log restores


Restore a Database

Restore your fulls and differentials with NORECOVERY, then restore the last part with RECOVERY to bring it up to the last transaction.

Revert a database snapshot

Reverting a snapshot overwrites the files, the logs, everything. Do a log backup first.

USE master;

Perform Piecemeal restores

  • Lets you restore the database in stages.
  • Only works with multiple filegroups
  • You can gets parts of the DB online while restoring less important parts

Perform Page Recovery

The database engine keeps track of corrupt pages in the msdb suspect_pages table.  Assuming the DB page verification is set to CHECKSUM or TORN_PAGE_VERIFICATION.

If you only have a few corrupt pages, you can just restore those instead of the whole database.  If the torn pages are in an index, just rebuild the index.

USE [msdb];
-- find corrupted pages
SELECT database_id' file_id' page_id' event_type' error_count' last_update_date
FROM dbo.suspect_pages
WHERE database_id = DB_ID('WorldWideImporters');
-- restore 4 corrupt pages
USE [master];
RESTORE DATABASE [WorldWideImporters] PAGE='1:300984' 1:300811' 1:280113' 1:170916'
     FROM WideWorldImporters_Backup_Device

Perform point-in-time recovery

Any transactions after the point in time will be lost.  This is handy in case a user accidentally deleted a table or something.  Not commonly used in real life.  Do a point in time restore to a non-prod database

Three ways to do a PiT restore:

  1. Date/Time – a specific date and time
  2. Log Sequence Number (LSN) – an internal numbering sequence for every operation in the transaction log
  3. Marked Transaction – an explicitly named transaction
  • STOPAT –
  • STOPATMARK- rolls the log forward to the mark, and includes the marked transaction
  • STOPBEFOREMARK – rolls the log forward to the mark, excludes the marked transaction
RESTORE LOG WorldWideImporters
     FROM WorkdWideImporters_Log_Backup_Device
     STOPAT = 'Sep 17' 2016 2:30 AM';

Restore a filegroup

  1. Backup the tail-log
  2. Restore the file group
  3. Restore and different backups
  4. restore the log-chain to bring the filegroup in sync with the rest of the files in the DB
  5. Stop at a point in time if necessary
  6. Recover the filegroup and bring it online

Can be done online in Enterprise Edition, must be done offline in others.

Develop a plan to automate and test restores

You need to know how long your restores take, so you know if you can meet your SLAs