SQL Server database three recovery models

Three recovery models for SQL SERVER 2005: simple (Sample), full, high-volume (bulk_logged)

Full Backup model

Full backup mode is the minimum risk of data loss when data file corruption occurs. If a database is in this mode, all operations are fully logged, which means that each row added with the insert operation is logged, each row deleted with the delete operation and each row modified with the update operation, SQL Server will also use BCP and bulk Each row inserted by the insert operation is completely recorded in the things log. If you have been doing regular transaction log backups after a database backup, then we can restore the database to a state at any point in time before the last log backup. Also, if a log file is available after the data file fails, we can restore the db to the state of the last log submitted before the failure. SQL SERVER2005 later also supports a feature that becomes a log ID (logfile MARKS), which allows us to place some reference points in the transaction log. If the data is in full recovery mode, we can choose to restore the status when a log flag is restored.

Under the full recovery model, SQL SERVER also fully records the Create Inddex operation, and when recovering from a transaction log backup that contains the creation index, the recovery operation is very fast because the index does not have to be rebuilt---All index pages have been fetched as part of the database backup. Before SQL Server 2000 SQL Server logged only one of the facts that had been created, so that when we recovered from the log file backup, the entire index would have to be re-established.

It looks like the full recovery model is the best, but there are other costs to pay for it. One of the biggest standby is the transaction log is quite large.

Large-scale recovery model (BULK logged)

The high-volume recovery model allows us to completely restore the database in the event of a media failure, and it also provides the best performance and minimal log space for some large-volume operations. These high-volume operations include BULK INSERT, BCP, CREATE INDEX, SELECT into, WRITETEXT, and UPDATETEXT. For the full recovery model, these operations are completely logged, but for the large-volume recovery model, they are recorded in the smallest quantity.

When you perform one of the operations in a bulk operation, SQL SERVER records only the information that the operation has ever occurred and about the allocated space for the operation. However, because SQL Server has tracked the bulk operation to actually modify those extents, the operation can still be fully recovered. Every piece of data in the database I have at least one of them. A page that becomes a large volume change map (BULK, BCM), and SQL Server manages the page like GAM Sgam.

Because it is possible to record a large number of operations in a small amount, these operations can be much faster than when the full backup mode is performed. It takes some overhead to set each flag bit on the correct BCM page, but it is much less expensive to flip these bits than to record each individual change to the data and index.

If the database is in bulk logged mode and does not actually perform any bulk operations, thinking that the log will contain a complete sequential record of all changes to the database, we are able to restore the database to either a point in time or to name the log identity.

There is some overhead during the backup log, in addition to copying the contents of the transaction log to the backup media, SQL Server scans the BCM page and backs up the transaction log while backing up the modified extents. The log file itself remains small, but the log backup may be many times larger than the log file, so the log backup takes more time and may consume more space than the full mode. Restoring a log backup that was made in high-volume mode is basically the same time as restoring a log backup that was made in full mode. The restore process does not need to redo individual operations, and the information required to recover all data and index structures is included in the log backup.

Simple recovery Model (Sample)

Simple recovery Model (Sample)

The simple recovery model provides the simplest backup----recovery strategy. Whenever a checkpoint occurs (checkpoints periodically and frequently occur), the transaction log is staged. As a result, the types of backups that can be backed up are those that do not require log backups. These types of backups have full backup database backups, incremental backups, partial full backups, partial incremental backups, and filegroup backups for read-only filegroups. In the simple recovery model, if you attempt a log backup, you receive an error message because the backup of the pattern does not require a log, and once all of the transactions it contains are committed or rolled back, the parts of the log can be re-used, and the server or the event fails to recover without the need for these things information. In fact, once the database is changed to Simple mode, the log is truncated.

Note: Simple mode is not logging. The so-called "simple" is that our backup strategy does not need to worry about log backups, although a single log record is not as good as in full backup mode, but in simple mode, all operations will be logged. Because the high-volume operations discussed in the high-volume recovery model are also recorded here at a minimum, the log for a database in simple mode may not grow as fast as the log in full mode. This is not to say that in the simple mode we do not have to relate the size of the log file. Just as in any recovery model, the log record of the active food cannot be truncated and the log of everything that starts after the oldest active food cannot be truncated. Therefore, if you have large or long-time transactions, you will still need a lot of log space.

SQL Server database three recovery models