Design a Restore Strategy
In case of disaster:
- Do not panic
- Think about people, processes, and recovery assets
- Stop access to the DB
- See if there is a tail-log
- backup the tail-log (if you can)
- access damage / corruption
- Assess whether a restore strategy is required
- Assess whether point-in-time recovery is required
- Assess what backups exist
- Verify backups are current and valid
- Plan restore strategy
- Initiate restore process
RESTORE command takes some arguments:
- FILELISTONLY – list of database and log files contained in the backup
- HEADERONLY – returns backup header info for all backup sets
- LABELONLY – lists media information
- VERIFYONLY – verifies the backup, but doesn’t restore it
- MOVE – relocates the database’s files
- NORECOVERY -will not roll back uncommitted transactions
- RECOVERY – rolls back uncommitted transactions
- REPLACE – bypass internal safety check. Overwrites without mercy
- 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; GO RESTORE DATABASE [WHATEVERDATABASE] FROM DATABASE_SNAPSHOT = 'WHATEVERDATABASE_2018_12_05'
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]; GO -- 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'); GO -- restore 4 corrupt pages USE [master]; GO RESTORE DATABASE [WorldWideImporters] PAGE='1:300984' 1:300811' 1:280113' 1:170916' FROM WideWorldImporters_Backup_Device WITH NORECOVERY; GO
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:
- Date/Time – a specific date and time
- Log Sequence Number (LSN) – an internal numbering sequence for every operation in the transaction log
- 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 WITH FILE=6' RECOVERY' STOPAT = 'Sep 17' 2016 2:30 AM';
Restore a filegroup
- Backup the tail-log
- Restore the file group
- Restore and different backups
- restore the log-chain to bring the filegroup in sync with the rest of the files in the DB
- Stop at a point in time if necessary
- 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