Backup and Recovery - II

1
Backup and Recovery - II

• Checkpoint
• Transaction log active portion
• Database Recovery

SQL 710
2
Checkpoints

• A checkpoint causes modified (i.e. dirty) data
  and log pages from the buffer cache of the
  current database to be written to disk this is
  called flushing
• This minimizes the number of modifications that
  have to be rolled forward during a recovery
• Log file is sequential and every record has a
  Log Sequence Number (LSN)

SQL 710
3
Checkpoint

• Checkpoints occur
• when a CHECKPOINT statement is executed (only
  the current database is checkpointed)
• when ALTER DATABASE is used to change a database
  option
• when SQL Server is stopped by SHUTDOWN
  statement or by using SQL Server Service Manager
  to stop running an instance of database

SQL 710
4
Checkpoint Processes

• A SQL Server 2000 checkpoint performs these
  processes in the current database
• Writes to the log file a record marking the start
  of the checkpoint
• Stores information recorded for the checkpoint in
  a chain of checkpoint log records. The LSN of the
  start of this chain is written to the database
  boot page
• Records the LSN of the first log image that must
  be present for a successful database-wide
  rollback.

SQL 710
5
Checkpoint Processes (ctd)

• A SQL Server 2000 checkpoint also performs these
  processes in the current database
• Records a list of all outstanding, active
  transactions
• Determines the Minimum Recovery LSN (MinLSN)
• Deletes all log records before the new MinLSN if
  using simple recovery model
• Writes to disk all dirty log and data pages
• Writes to log file a record marking the end of
  checkpoint.

SQL 710
6
Minimum LSN

• MinLSN is the minimum of
• LSN of the start of the checkpoint
• LSN of the start of oldest active transaction
• LSN of the start of the oldest replication
  transaction that has not yet replicated to all
  subscribers

SQL 710
7
Active Portion of Trans Log

• Portion of the log file from the MinLSN to the
  last-written log record is called the active
  portion of the log
• This is the portion of the log required to do a
  full recovery of the database
• No part of the active log can ever be truncated
• All log truncation must be done from the parts
  of the log before the MinLSN.

SQL 710
8
MinLSN Example

• See BOL - active portion of log for illustration
  of a simplified version of the end of a
  transaction log with 2 active transactions where
  
• LSN 148 is last record in the transaction log
• Checkpoint was recorded at LSN 147
• At checkpoint, Tran 1 had been committed and
  Tran 2 was the only active transaction
• First log record for Tran 2 is oldest log record
  for active transaction at time of last checkpoint
• Begin transaction record for Tran 2, LSN 142, is
  the MinLSN for this checkpoint

SQL 710
9
Recovery

• Recovery ensure data is in a consistent state
• Each time an instance of SQL Server starts, it
  recovers each database by rolling back
  transactions that did not commit and rolling
  forward transactions that did commit but whose
  changes were not yet written to disk when an
  instance of SQL Server stopped
• use recovery interval database option to set the
  maximum number of minutes needed to recover
  database (default is 0 with recovery time under 1
  minute and checkpoint about every minute for an
  active database)

SQL 710
10
Recovery to a Point in Time

• You can recover to a point in time by recovering
  only the transactions that occurred before a
  specific point in time within a transaction log
  backup, rather than the entire backup. By viewing
  the header information of each transaction log
  backup or the information in the backupset table
  in msdb, you can quickly identify which backup
  contains the time to which you want to restore
  the database. You then need only apply
  transaction log backups up to that point.

SQL 710
11
Point in Time Recovery (ctd)

• Cannot skip specific transactions because any
  transactions that occur after the transaction you
  want to undo might depend on the data modified by
  the undone transaction
• This would compromise the integrity of the data
  in the database

SQL 710
12
Point in Time Recovery (ctd)

• If you do not want to restore any modifications
  made to the database after a specific point in
  time
• Restore last database backup without recovering
  the database
• Apply each transaction log backup in the same
  sequence in which they were created
• Recover the database at the desired point in time
  within a transaction log backup.
• This can also be used to restore a database and
  transaction logs if some log backups created are
  missing or damaged

SQL 710