A recovery model is a database configuration option, chosen when creating a new database, which determines whether or not you need to (or even can) back up the trans-action log, how transtrans-action activity is logged, and whether or not you can perform more granular restore types that are available, such as file and page restores. All SQL Server database backup and restore operations occur within the context of one of three available recovery models for that database.
• SIMPLE recovery model – certain operations can be minimally logged. Log backups are not supported. Point-in-time restore and page restore are not supported. File restore support is limited to secondary data files that are designated as READONLY.
Chapter 1: Basics of Backup and Restore
• FULL recovery model – all operations are fully logged. Log backups are supported.
All restore operations are supported, including point-in-time restore, page restore and file restore.
• BULK_LOGGED recovery model – similar to FULL except that certain bulk operations can be minimally logged. Support for restore operations is as for FULL, except that it's not possible to restore to a specific point in time within a log backup that contains log records relating to minimally logged operations.
Each model has its own set of requirements and caveats, so we need to choose the appro-priate one for our needs, as it will dramatically affect the log file growth and level of recoverability. In general operation, a database will be using either the SIMPLE or FULL recovery model.
Can we restore just a single table?
Since we mentioned the granularity of page and file restores, the next logical question is whether we can restore individual tables. This is not possible with native SQL Server tools; you would have to restore an entire database in order to extract the required table or other object. However, certain third-party tools, including Red Gate's SQL Compare, do support object-level restores of many different object types, from native backups or from Red Gate SQL Backup files.
By default, any new database will inherit the recovery model of the model system database. In the majority of SQL Server editions, the model database will operate with the FULL recovery model, and so all new databases will also adopt use of this recovery model. This may be appropriate for the database in question, for example if it must
support point-in-time restore. However, if this sort of support is not required, then it may be more appropriate to switch the database to SIMPLE recovery model after creation.
This will remove the need to perform log maintenance in order to control the size of the log. Let's take a look at each of the three recovery models in a little more detail.
Simple
Of the three recovery models for a SQL Server database, SIMPLE recovery model databases are the easiest to manage. In the SIMPLE recovery model, we can take full database backups, differential backups and file backups. The one backup we cannot take, however, is the transaction log backup.
As discussed earlier, in the Log space reuse section, whenever a CHECKPOINT operation occurs, the space in any inactive portions of the log file belonging to any database operating SIMPLE recovery model, becomes available for reuse. This space can be overwritten by new log records. The log file does not and cannot maintain a complete, unbroken series of log records since the last full (or differential) backup, which would be a requirement for any log backup to be used in a point-in-time restore operation, so a log backup would be essentially worthless and is a disallowed operation.
Truncation and the size of the transaction log
There is a misconception that truncating the log file means that log records are deleted and the file reduces in size. It does not; truncation of a log file is merely the act of making space available for reuse.
This process of making log space available for reuse is known as truncation, and databases using the SIMPLE recovery model are referred to as being in auto-truncate mode.
In many respects, use of the SIMPLE recovery model greatly simplifies log file
management. The log file is truncated automatically, so we don't have to worry about log file growth unless caused, for example, by some large and/or long-running batch operation. If a huge number of operations are run as a single batch, then the log file can grow in size rapidly, even for databases running in SIMPLE recovery (it's better to run a series of smaller batches).
Chapter 1: Basics of Backup and Restore
We also avoid the administrative burden of scheduling and testing the log backups, and the storage overhead required for all of the log backup files, as well as the CPU and disk I/O burden placed on the server while performing the log backups.
The most obvious and significant limitation of working in SIMPLE model, however, is that we lose the ability to perform point-in-time restores. As discussed earlier, if the exposure to potential data loss in a given database needs to be measured in minutes rather than hours, then transaction log backups are essential and the SIMPLE model should be avoided for that database.
However, not every database in your environment needs this level of recoverability, and in such cases the SIMPLE model can be a perfectly sensible choice. For example, a Quality Assurance (QA) server is generally subject to a very strict change policy and if any changes are lost for some reason, they can easily be recovered by redeploying the relevant data and objects from a development server to the QA machine. As such, most QA servers can afford to operate in SIMPLE model. Likewise, if a database that gets queried for infor-mation millions of time per day, but only receives new inforinfor-mation, in a batch, once per night, then it probably makes sense to simply run in SIMPLE model and take a full backup immediately after each batch update.
Ultimately, the choice of recovery model is a business decision, based on tolerable levels of data loss, and one that needs to be made on a database-by-database basis. If the business requires full point-in-time recovery for a given database, SIMPLE model is not appropriate. However, neither is it appropriate to use FULL model for every database, and so take transaction log backups, "just in case," as it represents a considerable resource and administrative burden. If, for example, a database is read-heavy and a potential 12-hours' loss of data is considered bearable, then it may make sense to run in SIMPLE model and use midday differential backups to supplement nightly full backups.
Full
In FULL recovery model, all operations are fully logged in the transaction log file. This means all INSERT, UPDATE and DELETE operations, as well as the full details for all rows inserted during a bulk data load or index creation operations. Furthermore, unlike in SIMPLE model, the transaction log file is not auto-truncated during CHECKPOINT opera-tions and so an unbroken series of log records can be captured in log backup files.
As such, FULL recovery model supports restoring a database to any point in time within an available log backup and, assuming a tail log backup can be made, right up to the time of the last committed transaction before the failure occurred. If someone accidentally deletes some data at 2:30 p.m., and we have a full backup, plus valid log backups spanning the entire time from the full backup completion until 3:00 p.m., then we can restore the database to the point in time directly before that data was removed. We will be looking at performing point-in-time restores in Chapter 6, Log Restores, where we will focus on transaction log restoration.
The other important point to reiterate here is that inactive VLFs are not truncated during a CHECKPOINT. The only action that can cause the log file to be truncated is to perform a backup of that log file; it is only once a log backup is completed that the inactive log records captured in that backup become eligible for truncation. This means that log backups play a vital dual role: firstly in allowing point-in-time recovery, and secondly in controlling the size of the transaction log file.
In the FULL model, the log file will hold a full and complete record of the transactions performed against the database since the last time the transaction log was backed up.
The more transactions your database is logging, the faster it will fill up. If your log file is not set to auto-grow (see Chapter 3 for further details), then this database will cease to function correctly at the point when no further space is available in the log. If auto-grow is enabled, the log file will grow and grow until either you take a transaction log backup or the disk runs out of space; I would recommend the first of these two options.
Chapter 1: Basics of Backup and Restore
In short, when operating in FULL recovery model, you must be taking transaction log backups to manage the growth of data in the transaction log; a full database backup does not cause the log file to be truncated. Once you take a transaction log backup, space in inactive VLFs will be made available for new transactions (except in rare cases where you specify a copy-only log backup, or use the NO_TRUNCATE option, which will not truncate the log).