Commenting on the recovery backup mode for SQL Server 2008

Brief introduction

This article explores the choice of the recovery model used by SQL Server 2008 and the various methods that can be used to back up a database, such as differential backups and things log backups. In addition, transaction log backups will be introduced, which makes the recovery model possible. backups, transaction logs, and recovery models are interconnected when one of the settings will affect the other option.

In previous articles (SQL 2008 backup and Restore), full backups and many existing alternative configurations were described. Detailed descriptions are made from the backup command line and SQL Server Management Studio. In addition, the new backup compression in SQL Server 2008 is also described.

Transaction log

Each SQL Server database contains at least two physical files, an MDF file, and a LDF file on your hard disk. The MDF file contains all the actual data that is stored. A LDF file or log file that contains a record of each data change. Logging each data change makes the undo operation and the Point-in-time backup possible. A point-in-time backup allows us to recover any point-in-time database we want, yesterday, two hours ago, two minutes ago. By default, the MDF and LDF files are located in the Server\version\mssql\data directory under SQL Server on your hard disk.

Although this is a historical record, it cannot be stored in the things log file forever. If you do this, the size of the log file will become huge and uncontrollable over time. Instead, log files should be cleaned up regularly, or "abridged". How much time between deletions allows log file growth to be determined by the database "recovery Model".

Recovery mode

A "recovery mode" determines how the transaction log of a database is saved. Each database has its own recovery mode settings. This means that SQL Server can contain multiple databases, each of which has its own recovery model, and is unique to the configuration of other databases.

To view a database recovery mode setting, open SQL Server server Management, right-click a database, and then select Properties. Once the Properties dialog box is open, select options from the menu on the left.

The recovery model can be complete, simple, or batch log recovery.

Simple recovery

When a database is set to a simple recovery model, this means that the log file will not be permanently saved. Therefore, when executing a TSQL declaration, any changes are written to the data and log files, but these log files will not be saved for a long time before being truncated (cleaned up). This abridged SQL Server issue "Checkpoint" caused by. For a complete description of the checkpoint event, see the Microsoft article: http://msdn.microsoft.com/en-us/library/ms188748.aspx. The truncated log file means to us that these log files cannot be used for database recovery. This is because we cannot control when the log will be purged. Therefore, when a database is set to simple recovery, the only optional backup type is a full backup.

Full backup restores all your data, but you cannot ask it to recover all of my data at a particular point in time. For point-in-time recovery, we need a log of things.