SQL Server 2012 notes Sharing-22: Understanding Backup Mode and Recovery model

The SQL Server backup and restore component provides basic security for protecting critical data stored in a SQL Server database. To minimize the risk of catastrophic data loss, you need to back up the database regularly to preserve the changes made to the data. A well-planned backup and restore strategy helps prevent SQL Server 2012 notes Sharing-22: Understanding the Backup mode and recovery model database data loss due to various failures. Test your strategy by restoring a set of backups and then recovering the database to prepare for an effective response to the disaster.

=============================================================================================================== ======

Backup Concepts

A full backup includes a backup of the entire database, part of the transaction log, the database structure, and the file structure.

Differential backup refers to the backup of all changed data after the last full backup.

A transaction log backup records all changes to the database, divided into:

Back up the transaction log: From the last successful transaction log backup to the tail of the current transaction log.

Truncate transaction log: The inactive part is discarded at the start of the transaction Log Activity section.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://img1.51cto.com/attachment/201406/16/639838_1402879328OWGb.png "height=" 484 "/>

Recovery mode

Simple mode: Applies to small databases that are infrequently updated, does not support log backups, loses data changes after the last backup, and cannot be restored to immediate point.

Bulk-Logged Mode: is a minimized log backup that may have data loss and cannot be recovered to a point-in-time, typically used for temporary mass data import.

Full mode: Support log backup, no data loss, can be restored to the point of instant, generally used in production database.

For production databases It's generally best to work in the full recovery model, you can switch to the bulk-logged recovery model when you need to perform bulk-logged operations

650) this.width=650; "title=" clipboard[1] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [1] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402879329MKmh.png "height=" 484 "/>

Recovery options

When more than one backup needs to be restored, all backups except the last one must be specified as no recovery when restoring.

Restore with recover: You cannot restore other transaction logs by rolling back uncommitted transactions so that the database is in a ready state.

Restore WITH NORECOVERY: You can restore other transaction logs without performing any operations on the database, not rolling back uncommitted transactions.

Restore with Standby: Causes the database to be in read-only mode, revoking uncommitted transactions, but saving the undo operation in the backup file so that the recovery effect can be reversed.

650) this.width=650; "title=" clipboard[2] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [2] "src=" Http://img1.51cto.com/attachment/201406/16/639838_1402879332EQ90.png "height=" 217 "/>

==========================================================================================================

Summary of Points

Log backups must be guaranteed to be sequential in their order. If there is a problem with the intermediate backup, the restore operation is in trouble.

Do not put databases and backups on the same disk.

A unique binary copy of all data in the database when the backup is made.

A backup set is a collection of several files that are acting on a backup. If you need to speed up your backups, you can choose to perform backups on multiple backup devices at the same time.

A physical backup device is a rank used by the operating system to identify the backup device, such as C:\xxx\backup.bak.

A logical backup device is a user-defined alias used to identify a physical backup device. Logical device rankings are permanently stored in the system tables in SQL Server.

You can back up to a network share location, but the method lacks reliability. A good way to do this is to back up to the SQL Server local disk and then compress the backup to a different location. (Currently the latest version of SQL already supports compressed backup, and the mainstream enterprise backup software also supports compressed SQL backup).

SQL Server uses a backup history to maintain all databases, automatically identifies the most recent backup set, and appears in the Select Backup Set for Restore dialog box.

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!