Rookie Learn sqlserver--Recovery model

Recovery mode

SQL Server supports three recovery models:

• Simple-no log backups are supported, and automatic checkpoints truncate portions of the transaction log that are not used
• Complete-Requires a log backup. Support Point Recovery
• Bulk log-requires a log backup. Reduce the amount of log space usage by logging most bulk operations in a minimal way.

Different recovery models support different restore scenarios and also affect the management of transaction logs.

Restore Scenarios

By granularity, SQL Server supports the following types of restores:

• Database Full Restore
• Data file Restore
• Page restore

The specific restore scheme and the recovery model corresponding to the table can be found in the reference link 2.

Selection of recovery modes

• Simple Recovery Model

Easy to use, often used in development environments. No backup is required to restore the transaction log, so the risk of data loss is assumed.

• Full Recovery model

Make sure you can recover to the point of failure, but consider transaction log backups.

• Bulk-Logged Recovery model

Because bulk operations increase the overhead of log backups, the bulk recovery model minimizes logging for most bulk operations. Point Recovery not supported

Reference:

[1] Recovery Model Overview https://msdn.microsoft.com/zh-cn/library/ms189275 (v=sql.105). aspx

[2] Overview of Restore and Recovery https://msdn.microsoft.com/zh-cn/library/ms191253 (v=sql.105). aspx

[3] Select the database recovery Model https://msdn.microsoft.com/zh-cn/library/ms175987 (v=sql.105). aspx

Rookie Learn sqlserver--Recovery model