prevent recovery amnesia - forget the backups
TRANSCRIPT
Prevent Recovery AmnesiaForget The Backups
Recovery Objectives
RTO
Amount of time which data or hardware is desired to be restored after data corruption or hardware failure
Usually referred to in 9’s
• 5 9’s = 99.999 % up time• Just over 5 minutes of allowable downtime per year (means
maintenance)
• 4 9s = 52.5 minutes
• 3 9s = 8.75 hrs
• 5 9s between 9-5 different than 5 9s 24/7
RPO
Point in time that data can be restored from.• Minutes
• Hours
• Days
• Weeks
Basically – How much work can be lost?
SLA
RPO & RTO together = Service Level Agreement
• Need to know for each database you are responsible for
• Press business managers / owners for business driven decisions on what acceptable values are
• MUST consider the business implications of downtime and data loss
Recovery Strategy
Recovery Strategy
Backups are worthless• If can’t restore to meet business requirements
Most critical of DBA duties
Practice, practice, practice
Practice different scenarios• Point in time
• Corrupt page
Don’t forget the Keys!
Keys & Offsite Storage
Backup certificates and keys• First thing done on any server
• If lost, data could be too
Keys to backup• Service Master Key
• Database Master Key
• Symmetric, Asymmetric & Certificates
Offsite storage• Necessary evil
• Mitigate the risks
Backup Models
Simple
No log backupAuto reclaims
Changes since most recent backup unprotected
Only recover to end of backup
Full
Requires log backups
No work lost due to lost or damaged data file
Restore to an arbitrary point in time
Normally no loss of data, but slight risk
Bulk Logged
Special purpose
Reduce log space
No point in time recovery supported
Temporary use for bulk operations
Demo
Log • Growth
• Free space
Backup Scope
Database
• Entire database
• Includes• Schema
• Data contents
• Transaction log to restore from scratch
• Simplest way to restore
• Lot of disk space
• Lot of time
Partial
Alternative to database backups for VLDB
Good for read-only data
Includes• Primary filegroup
• All read/write filegroups
• Specified read-only filegroups
File
Individual files and / or filegroups
Compliment partial backups
Usually included in complex backup models
Backup Types
Full
All data within the backup scope
Entire contents of files and filegroups
Does not truncate transaction log• Simple recovery model only
Base for other backup types
Differential
Only what changed since last full backup
Faster
Must restore full backup first
Log
Transaction log
Must have full backup first
Not an option with simple recovery mode
Truncates log by writing committed transaction to disk
Tail Log
Captures log records not yet backed up
Keeps log chain intact
Required to recover to latest point in time
Not all scenarios require
Demo
Restore• Point in time
Compression
Compression
Saves space & I/O
Heavy impact on CPU
Set at server level• Time of backup
Compression ratio• Msdb.dbo.backupset
• Backup_size / compressed_backup_size
Encryption
Encryption
New for SQL 2014
Encrypt while backing up
Requires Database Master Key• Certificate
• Asymmetric key (EKM only)
If using TDE • Use different certificates & keys to increase security
More for 2014
New backup Features
SQL Server Backup to URL• SQL 2012 SP1 CU2
• T-SQL, PowerShell, SMO
• 2014 adds SSMS
SQL Server Managed Backup to Windows Azure• Database or Instance level
• Recommended for SQL Server instance on Azure VMs
Encryption for Backups
Backup Myths
Myths
Backup operations cause blocking
Concurrent transaction backup can’t be done when full/differential in progress
Backups always test page checksums
Backups read through the buffer pool
Full or differential backups clear the log
Backups perform consistency checks
If the backup works, so will the restore
Myths
Tail of log backups are always possible
Use snapshots instead of backups
Log backups are the size of the log
Cannot backup a corrupt database
Log backups always clear the log
No need to backup the system databases
Always plan a good backup strategy
Always plan a good recovery strategy!
