Microsoft SQL Server 2000 Database Recovery Backup and Restore Reinaldo Kibel Support Engineer Devel

1
Microsoft SQL Server 2000Database Recovery
Backup and RestoreReinaldo KibelSupport
EngineerDeveloper Support - SQL Server
Microsoft Corporation
2
Agenda

• Restore, have you tried it out?
• Microsoft SQL Server 6.5 backup mechanism
• SQL Server 7.0 backup mechanism
• Recovery models on SQL Server 2000
• Enhancements
• Differences between models

3
Take HomeRestore, have you tried it out?

• Importance of backing up and restoring a database
• Restore the master database also
• Test the backup by restoring to another server
• A backup procedure can only be validated after a
  successful restore

4
Disaster Recovery Options

• Log shipping - standby (warm backup) server
• Snapshot backups
• Cluster Server

5
SQL Server 6.5 and 7.0What did we have?

• Synchronization point for full backup
• Version 6.5 Before reading first database page
• Version 7.0 After reading last database page
  (fuzzy backup)
• Types of backups
• Version 6.5 Full DB and log dumps
• Version 7.0 Full DB, differential DB, filegroup
  and log backups
• Version 7.0 has VDI support for ISVs
  (C\MSSQL7\DevTools\Samples\Backup)

6
SQL Server 2000 Recovery ModelsEnhancements

• Simplify recovery planning
• Simplify backup and restore procedures
• Clarify tradeoffs between the following
• Performance versus
• Data loss exposure
• Log space consumption
• Simplicity
• Easy transitions between common operational
  scenarios
• Maintain data protection
• Maintain availability
• Minimize disruption of automated maintenance
  procedures

7
Recovery Models

• Control media recovery only
• Unchanged All models support crash recovery
  without loss of committed work
• Choosing a model
• Key benefits
• Work loss exposure
• Functional limitations
• How it works
• Available backup types for each model
• Transitions

Recovery Models
Full
Simple
Bulk Logged
8
Key Benefits

• Full
• Minimal work loss exposure
• Supports recovery to arbitrary point-in-time
• Bulk_Logged
• High performance bulk operations
• Minimal log space for bulk operations
• Simple
• Simplest backup and restore strategy
• Less log space required

9
Work Loss ExposureBecause of media failure

• Full
• Normally none
• If log is damaged, redo changes since the most
  recent log backup
• Bulk_Logged
• Redo changes since last log backup if
• The log is damaged
• -or-
• The damaged files were subject to bulk operations
  that committed after the last log backup 
• Otherwise, no work is lost
• Simple
• Redo changes since the last database or
  differential backup

10
Work Loss ExposureBecause of media failure
(continued)

• Data (.mdf) and transaction log (.ldf) files are
• located in different physical disk drives. Data
• drive failed
• Only for Full or Bulk_Logged mode
• Back up the currently active transaction log by
  running BACKUP LOG DatabaseName TO
  LogBackupDevice WITH NO_TRUNCATE
• Restore most recent full database backup.
• If there are differential backups, restore them
• Apply all the transaction log backups, including
  the one created in Step 1 above.

11
Limitations

• Point-in-time
• Full allows recovery to arbitrary point-in- time
• Under other models must apply most recent backup,
  and then redo work
• Usability of log marks
• Marks are unusable
• Under the Simple model
• Under the Bulk_ Logged model, if a Bulk_Logged
  operation occurs in the log backup interval
• Log marks are currently not placed if known to be
  unusable

12
How?

• Full All operations are fully logged
• Bulk_Logged Minimal logging for some operations
• CREATE INDEX
• Bulk Load
• SELECT INTO
• WRITETEXT, UPDATETEXT
• But not controllable per operation because of
  recovery (DBA) impact
• Simple Log truncation on checkpoint

13
Log Backups in Bulk_Logged Mode

• Log backup contains extents modified by bulk
  logged operations since log backup
• Avoids breaking the log chain
• Eliminates need for special full backup
• Minimal impact on automated backups

Transaction Log
Log Backup
Data Extents
Extent modified by bulk logged operation
14
Available Backup TypesFor each model
15
Changing Recovery Model

• Changing back and forth between Full and
  Bulk_Logged transitions does not break automated
  backup procedures
• Transitions to and from Simple require more care
  but are uncommon

16
Setting the Recovery Model

• ALTER DATABASE DatabaseName ltrecovery_optiongt
  RECOVERY FULL BULK_LOGGED SIMPLE
• Example
• To select Full recovery modelALTER DATABASE
  DatabaseName SET RECOVERY FULL        
• To select Bulk_Logged recovery modelALTER
  DATABASE DatabaseName SET RECOVERY
  BULK_LOGGED        

17
Recovery Models and Legacy Options

• As installed, the model database uses the FULL
  recovery model

18
Backup and RestoreNew Features

• Implementation changes to differential backup
• Recover to mark
• Fast failback from standby to primary
• Snapshot backup and recovery
• Improved recovery from isolated application or
  operator errors
• Improved security for backups
• Other enhancements

19
Differential Backups

• FastBitmap driven
• NewDifferential file and/or filegroup backup for
  VLDB

Bitmap
Extents changed since database backup
Backup
20
Log MarkersFor recovery to a named event

• Transactions can place a named mark in the log
• BEGIN TRANSACTION TransactionLabel WITH MARK
• Similar to point-in-time recovery
• Recovery can include or exclude the named
  transaction

TransactionLabel transaction
Recover before mark to exclude risky work that
was a mistake
Recover at mark to include specific work
21
Log Markers (continued)

• Names can be reused
• A date and time can be given at restore time to
  further qualify the nameRESTORE LOG WITH
  STOPBEFOREMARK 'TransactionLabel' AFTER
  11/27/00 2213
• Marks are recorded in MSDB
• Restore history tracks restore to mark

22
Related DatabasesRecovery Challenge

• Prior versions of SQL Server do handle
• Crash recovery
• Media failure, if the transaction log is not
  damaged
• Prior versions of SQL Server do not allow
• Point-in-time recovery of related databases

SQL Server 2000 does!
23
Backup StrategyFor related databases

• Full recovery model
• Synchronized backups unnecessary
• Use recovery to mark
• Back up related databases in rotation
• Variations
• Back up infrequently modified databases less
  often
• Overlap as backup hardware allows
• Manage backups to achieve recovery goals
• Allow for roll forward time
• Worst casesimultaneous restore and recovery of
  all related databases

24
Log ShippingFast Failback

• Database restore not required, if the data and
  log files are not damaged
• BACKUP LOG TO WITH NORECOVERY STANDBY
• Log backups taken on secondary can be applied
  back on primary
• Very useful for planned failover
• Enables high availability reporting server to
  switch between primary and secondary

25
Snapshot Backup and Restore

• Functionally equivalent to full database, or file
  and filegroup backups
• Roll forward using conventional differential and
  log backups
• Can be used to initialize standby database
• History maintained in MSDB
• Third-party VDI application with storage system
  support (split mirror or copy-on-write)

Standby
Primary
Storage
Virtual Device Interface for Backup
26
Snapshot ExampleStandby Server using EMC2
Storage

• Split mirror during database backup
• Mount volume during database restore to standby
• Conventional log backups to disk are transferred
  the same way

Primary
Standby
Disaster Recovery Site
Volume
Split Mirror
Remote Mirroring
EMC Corp Symmetrix with SRDF and TimeFinder
27
Partial Database Restore

• Restores a subset of the filegroups
• Intended for recovery from operator or
  application error
• Dropped table
• Incorrect update

Backup of Database A
Database B
Partial Restore
Merge or replace damaged data
Database A
28
Log and File Backup

• Flexibility
• Log and file backups do not block each other
• Files are independent of each other
• Must do concurrent file backups as a single
  operation

Time
Log
Log
Log
Log
Log
Log
File
File
File
File
29
Backup Security

• Password protect
• Backup media
• Backup sets
• Nothing can be done if you forget it
• Prevents unauthorized restore using our tools
• Not encrypteddata can be interpreted by another
  program
• Prevents unauthorized append to media by our
  tools and other MTF-compliant applications
• Does not protect against media overwrite

30
Other Backup and Restore Improvements

• Online backups of large data files during heavy
  update
• Even less transaction throughput drop than SQL
  Server 7.0
• Higher average backup throughput
• The log can be backed up despite a damaged
  primary file
• Media recovery of add/drop file
• Maximum of 64 backup devices

31
Database Settings

• Improved usability
• All Database options are modified by using ALTER
  DATABASE
• Database options are available from built-in
  functions
• fn_DatabasePropertyEx() (individual values)
• fn_DatabaseProperties() (result set of all
  properties)
• sp_dboption and DatabaseProperty() are frozen at
  SQL Server 7.0 functionality
• RecoveryModel property
• The RecoveryModel property specifies the recovery
  model for a database and uses the syntax
  object.RecoveryModel value

32
Putting It All Together
Time
Full Recovery Model
Full Recovery Model
Bulk_Logged Recovery Model
OLTP
Bulk Load
Create Index
Online Check
Bulk Load
Create Index
Online Reorg
Log Backup
Log Backup
Log Backup
Log Backup
Log Backup
Log Backup
File Backup
File Backup
File Backup
File Backup
33
(No Transcript)