Blog
Protecting Your Data in SQL Server
By Don Kiely | December 10, 2012
The transaction log is fundamental to the way SQL Server works and is created automatically when you create a new database or attach an existing database that doesn't yet have a log file. The transaction log records almost all activity in the database and provides an extra layer of protection for your data. SQL Server uses a write-ahead strategy to maintain the log, taking the following actions for any data modification:
- Loads data pages into a buffer cache.
- Updates the copy in the buffer.
- Creates a log record in a log cache.
- Saves the log record to disk via the checkpoint process.
- Saves the data to disk.
If a system failure occurs, you can restore the database from a backup, then apply transactions from the transaction log to recover data changes that occurred after the last backup, and that would otherwise be lost. With complete backups of all transaction logs, you can restore the database to any point in time. This scheme provides remarkable resiliency to almost any kind of failure, short of a total system meltdown. And support is available even for surviving the devastating meltdown of a single server.
The transaction log is a separate data file (.ldf) that by default resides in the same location as the data file, but you'll often want to place log files on a separate device from the data files for additional security and performance. SQL Server supports three recovery models for recovering from a database server meltdown: simple, full, and bulk-logged.
- Simple: The log is truncated at a checkpoint, so that recovery takes the database back to the point of the last full or differential backup. A checkpoint is a known good point from which SQL Server can start applying changes from the log during recovery from a failure.
- Full: All database operations are fully logged so that you can recover the database to the point of failure.
- Bulk-Logged: Enables faster bulk-logged operations so that recovery is limited to logged transactions that occurred after the last backup.
TIP: The Recovery Model property of a database determines how the database uses its transaction log. A Simple setting for the Recovery Model keeps the log small by truncating the log after each checkpoint process. With this setting, the log supports each transaction while it is in progress, but database recovery relies on the last full backup of the database and cannot use the transaction log to restore data changes that occurred after the last backup.
This post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.
