Key Database Components for Backup and Recovery
The following diagram is used to illustrate the key components for backup and recovery. The diagram shows on a very simple level what happens when a record is modified in the database. Inserts, updates, and deletes will follow a similar process.
STEP 1: A user issues an update statement. If the record to be modified isn’t already in the buffer cache, a SQL Server process reads the required data page and places it into memory within the buffer cache.
STEP 2: The user modifies the record within the buffer cache. Modified pages within the buffer cache are marked as being ‘dirty’ and they will have to be written to disk in the future.
STEP 3: Immediately after the record is modified within the buffer cache, a corresponding record indicating the data change and the transaction that caused the data change is written to the log cache.
STEP 4: A checkpoint occurs, and log and data information must be written to disk. First, the log cache information is written to the transaction log
STEP 5: Dirty pages within the buffer cache are “flushed” to disk and written into the data files.
As a Database Administrator, it is imperative that you backup the data files and transaction logs frequently. Getting backups to these files is the key to backup and recovery. How frequently you perform these backups is dependent upon the business requirements of your organization.