SQL Server database local disaster preparedness scheme

　　If you ever had a casual heartbeat from database data corruption: Error additions, updates , deletions. Then the following plan must be able to heal your wounds!

For a database small white, any mistakes in the database can be said to be fatal. Initially, we let a child called " database " Wayward on the server naked ( without any data backup ), advocating freedom and openness. Suddenly one day, the child is sick ( data tampering ), very suddenly, very bad, he can not describe his feelings with you ( difference data ), then you will suffer from the diagnosis of the disease. If you now have a child's previous behavioral habits data ( complete, differential, log backup, tail-log backup ), then this will undoubtedly be quick for treatment and recovery (Recovery)! Sing it, Allah easy, where not to point!

Well, after the pleasure, let's settle down. The coloring of the backup literal.

1, complete: Full data backup (up to now point in time, Database complete data)

2, Differential: Differential data backup (the difference between the current point of time and the previous complete data)

3. Log backup: Transaction log data Backup (the difference between the current point in time and the last log backup)

4. Tail-Log backup: Manual backup Required (due to the difference between the time of the exception and the last log backup)

remark:　 (1), 1, 2, 3, 4 points are based on the database recovery model: Full mode (this mode generates a large number of logs and requires periodic maintenance logs)

(2), 2, 3, 4 points are based on point 1, that is, other backup dependencies at least once complete to perform

(3), 1, 2, 3 points can be backed up using SQL Server maintenance plan

(4), 1, 2 points of backup inside, although the data is complete, but the transaction log records are basically not, so to see historical data change behavior, must have a log backup

----------------------------------------------------------Gorgeous split line - ----------------------------------------------------------

Backup scenarios:

Complete (1 days/times) + differential (6 hours/times) + log backup (30 minutes/times) + tail-log backup (optional, for disaster use)

Note: (1), the backup interval depends on a number of factors, such as the importance of the data, the size of the database, and the workload of the server

(2), Apexsqllog is a good analysis of the log tool, has cracked version, support SqlServer2014

SQL Server database local disaster preparedness scheme