Transcript SQL 710

Backup and Recovery - II
- Checkpoint
- Transaction log – active portion
- Database Recovery
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)
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
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 databasewide rollback.
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.
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
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.
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
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)
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.
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
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