A must-have knowledge of SQL Server Backup and Recovery series

I. Backup overview

Data security is the life of the database, the database in the use of the process will inevitably encounter such as: the user's misoperation or malicious modification, hardware failure caused data files can not be accessed, natural disasters caused by physical damage to the computer room. This chapter takes the functionality of backup and recovery as a starting point for solving problems. In the actual work you will encounter: How to use the backup strategy (such as full backup, file backup, differential backup, log backup), how to reduce the backup recovery time (such as as soon as possible to return to the line), how to restore the database to the desired point in time (such as before the recovery to the wrong operation), how to migrate the database system such as user account, password, task script backup restore).

1. Backup Type

Include data files and log files in the SQL Server database, including data backup and log backups. A data backup can be a full database backup, a file backup, or a differential backup called an incremental backup. Log backups are also called transaction log backups.

Full backup	All the information in the database is recorded and the database data can be recovered to a point in time. However, a large database backup may Takes a long time. If the full backup type is used daily or hourly, a large amount of storage space and backup recovery time are required, and only full backups do not meet the needs of the user.
File backup	Backs up all data for one or more files or filegroups, mostly for large databases. File backup + log backup = Full backup. If a file is corrupted, simply restore the file, which speeds up the recovery.
Differential backup	Requiring a database to make a full backup before is called a baseline. It is the data that changed after the full backup. Facilitates frequent backups and reduces the risk of data loss.
Log backup	Requires a full backup of the database prior to the log record that was written since the last full backup or log backup. A continuous log chain can restore a database to any point in time. So play an important role in the backup strategy.

2. Backup Policy

(1) The maximum amount of data loss that can be tolerated by the database.
(2) How much manpower and resources are invested in database backup and recovery strategy.
(3) The more backup files, the more files the database recovers, to establish a proper backup management system. Although backups do not block the normal operation of the database, they result in a series of disk reads and writes, which should be avoided when the server I/O is busy. The more backups, the larger the overview of failures, requiring administrators to handle errors in a timely manner and restore the backup task to normal.

3. Common methods of Backup

Grade	Data backup		Log backup
Database-level	Full database backup	Differential database backup	Log backup
FILE-level	Full file backup	Differential file backup

　

Two. Backup types in the database recovery model

It says that there are several types of backups involved, and here's how the database recovery model supports and features backup types. SQL Server has three database recovery model settings including: Simple recovery model, full recovery model, and bulk recovery model.

2.1 Simple Recovery Model
In the simple recovery model, can not do log backup, only support the simplest way of backup and restore, easy to manage, the database after the last backup of the data modifications will be lost. To reduce risk, you can introduce differential backups. Differential backups are typically less expensive than full backups and can be run frequently. This simple recovery model is not appropriate if the database is large or does not allow for long-time data loss. In summary:

Advantages:

(1) log files occupy less physical space and log growth is slow.

(2) The performance of SQL performs excellent, can minimize the log.

Disadvantages:

(1) Log backups are not supported.

(2) can not achieve zero loss, recovery point in the first time when the backup.

(3) When switching to other recovery modes, the log chain is interrupted.

2.2 Large-capacity recovery model

Also known as the high-volume recovery model, you can use log backups, which provide the best performance and minimal log usage space for some high-volume operations, including BULK INSERT, Bcp,create index, select INTO, WRITETEXT, UPDATETEXT. This means that these operations have a risk of data loss, and that these operations are fully documented, relative to the full recovery model. Summary below:

Advantages:

(1) log files occupy less physical space (log growth is slow).

(2) Perform excellent performance on SQL (minimize log).

(3) Support switching to full mode does not interrupt the log chain.

Disadvantages:

(1) Restore large-volume operations, data loss risk such as BULK INSERT, select INTO and so on.

2.3 Full Recovery Model

It can also be called the full recovery mode, in which all operations are recorded in full, such as insert every new row, delete each deleted row, also includes a large number of operations such as bulk INSERT, etc., will be recorded in the transaction log. Including the CREATE index operation is also fully logged, and it is not necessary to rebuild the index when the log is restored, and recovery will be quick. With log backups, you can define a very frequent frequency, and 5 of clocks or even less time to do backups to prevent data loss from failure. However, the larger the number of backups, the recovery needs to be strictly in the order of backup to restore, in the middle cannot have any backup missing.

Advantages:

(1) The use of log backup can achieve zero loss (if you can make a tail-log backup, can be restored to any point in time).

(2) Support switching to large capacity mode does not interrupt the log chain.

Disadvantages:

(1) Log file space consumption must be a regular log backup to achieve log space reuse.

A must-have knowledge of SQL Server Backup and Recovery series