SQL ServerDatabase has three recovery modes: simple recovery mode, complete recovery mode and large-capacity log recovery mode.
1.Simple Simple recovery mode,
SimpleIn Simple mode, SQL Server automatically truncates the log after each checkpoint or backup.That’s to discard all inactive log records and keep only a small number of logs needed for instance recovery that occurs automatically when the instance starts. The advantage is that the log file is very small and does not require DBA to maintain and back up the logBut the downside is also obvious, that is, once the database is abnormal, need to restore, can only restore to the last backup, can not restore to the most recent available state, because the log lost. Simple mode is mainly used for non critical business, such as development library and test library.But Dow’s SQL Server (even production libraries) mostly uses Simple mode because SQL Server is mostly used for non-critical businesses (critical databases mostly use Oracle and DB2).You can tolerate less than 1 days of data loss (our job regularly back up the entire library every day).
If you need to compress database logs (Shrink statements), the compression rate is the highest after switching database mode to simple recovery mode. If your database is compressed in full recovery mode or bulk log recovery mode, the size of the compressed logs will not be ideal.
2.Full Full recovery mode,
Contrary to Simple mode, the old name for Full mode is “Checkpoint without truncate log” – that is, SQL Server does not actively truncate the log, only after backing up the log can truncate the log, otherwise LThe og file will continue to increase until the hard disk is burst, so it is necessary to deploy a job timing backup log. Full’s advantage is that it can do point-in-time recovery, to maximize the assurance that data is not lost, generally used in critical business environments. The drawback is D.BA needs to maintain logs and increase staff costs (that is, more regular backup of logs).
3.Bulk-logged Large capacity log recovery
Bulk-loggedSimilar to full mode, the only difference is that the following Bulk operations produce as few logs as possible: 1) Bulk load operations (bcp and BULK INSERT). 2) SELECT INTO.3) Create / drop / rebuild index is well known for the large number of logs generated by bulk operations, which have a significant impact on the performance of SQL Server. The bulk-logged pattern reduces this performanceIt can affect and prevent excessive log file growth, but its problem is that point-in-time cannot be restored to the time when the bulk-logged record is included. The best practice of Bulk-logged mode is before bulk operation.Switch to bulk-logged and switch back to full mode immediately after the bulk operation is finished.