Page 135 - DCAP508_DATABASE_ADMINISTRATION

Unit 9: Database Recovery Models

Log Sequence Number (LSN): The unique number that each operation is stamped with when it is Notes
written to the transaction log. A single SQL Server transaction may contain several LSNs.
Simple Recovery Model: The simple recovery model minimizes administrative overhead for the
transaction log, because the transaction log is not backed up.
SQL Query Analyzer: A graphical tool provided with SQL Server client utilities to query SQL
Server databases using the Transact-SQL commands.
Transaction: A set of modifications that are performed as a single unit of work. A transaction
follows the ACID guidelines

Transaction Log: A record of modifications performed to a database. The amount of information
logged in the transaction log depends on the recovery model for a database.
Tail of Transaction Log: The transactions that have been committed but not backed up since the
previous complete or differential database backup or transaction log backup.
Transaction undo File: File containing information regarding any modifications that were
made as part of incomplete transactions at the time the backup was performed. A transaction
undo file is required if a database is loaded in read-only state. In this state, further transaction
log backups may be applied.
Virtual Log File (VLF): A logical section within an SQL Server database’s transaction log. When
performing a truncate of the transaction log, an entire VLF is cleaned out.

9.8 Review Questions

1. Explain the need of database recovery.
2. Describe the various recovery models in SQL server.
3. Discuss Simple recovery model.

4. How will you use the full recovery model for database recovery?
5. Differentiate between the three database recovery model in SQL server.
6. Describe the process of changing the Recovery Model.
7. Explain the process of Switching from Full or Bulk-Logged to Simple Recovery.
8. Analyze the various terms used for database recovery model.

9. What are the two frequently used settings that database incorporates in the recovery
model? Discuss.
10. Illustrate the steps required in viewing or changing the recovery model of a database.

Answers: Self Assessment

1. Virtual Log File 2. Transactions
3. Log Sequence Number 4. SQL Query Analyzer
5. data page 6. Transaction log backups
7. Full 8. Bulk-Logged

9. Administration

LOVELY PROFESSIONAL UNIVERSITY 129