SQL Server的幾種恢復模式（recovery models）

Concept: Describe SQL Server recovery models.
Introduction
SQL Server has three database recovery models: simple, full, and bulk-logged. Each of the models
maintains data in the event of a server failure, but there are key differences between the recovery
models in how SQL Server recovers data.
You can set or change your recovery model at any time, but you should plan a recovery model when
you create a database.
Simple recovery model
You typically use the simple recovery model for small databases or databases in which data changes
infrequently. This model uses full or differential copies of the database and recovery is limited to
restoring the database to the point when the last backup was made. All changes made after the backup
are lost and need to be recreated. The principal benefit of this model is that it takes less storage space
for logs and is the simplest model to implement.
當資料庫比較小或者資料庫中的資料不是很頻繁的被改變，你可以使用簡單恢復模型。這種模型使用完全或差異資料庫複本恢復時受限於還原資料庫只能還原到資料庫最後備份的一個點。所有備份後做的改變將會丟失和需要重新建立。這種模型主要的好處是，它佔用的日誌儲存空間很小，並且是實施最簡單的模型。
Full recovery model
You can use the full recovery model when full recovery from damaged media is the highest priority.
This model uses copies of the database and all log information to restore the database. SQL Server
logs all changes to the database, including bulk operations and index creations. Provided that the logs
themselves are not damaged, SQL Server can recover all data except transactions actually in process
at the time of the failure.
你使用完全恢復模型從損壞的介質中完全恢復是最高優先順序的。這種模型使用資料庫和所有的日誌資訊複本去還原資料庫。SQL Server記錄了所對資料庫的改變，包含大量的操作與索引建立。只要日誌檔案本身沒有被損壞，SQL Server能恢復所有資料，除了在失敗時所做的處理。
Because all transactions are logged, recovery can be made to any point in time. SQL Server supports
the insertion of named marks into the transaction log to allow recovery to that specific mark.
Because log transaction marks consume log space, you should only use them for transactions that play
a significant role in the database-recovery strategy. The main limitation of this model is the large size
of the log files and the resulting storage and performance costs.
因為所有的事務被記錄了，所以能恢復到任何時間點。SQL Server支援插入標記到事件日誌中，以允許恢復到指定的標記處。因為日誌事務標記佔用了日誌的空間，你將唯一能使用它們的是事務在資料庫恢復策略中扮演了一個具有重要意義的角色。這種模型主要的侷限是，日誌檔案太大和導致增加了較多的儲存空間和效能的成本。
Bulk-logged recovery model
Similar to the full recovery model, the bulk-logged recovery model uses both database and log
backups to recreate a database. However, the bulk-logged recovery model uses less log space for the
following operations: CREATE INDEX, bulk load operations, SELECT INTO, WRITETEXT, and
UPDATETEXT. The log notes only the occurrence of these operations as bits in extents instead of
storing details of the operations in the log.
與完全恢復模型相似，大批日誌恢復模型使用了資料庫和日誌備份去重建資料庫。然則，大批日誌恢復模型針對下面的操作使用了較少的日誌空間：CREATE INDEX，bulk load operations,SELECT INTO,WRITETEXT和UPDATETEXT。日誌僅記錄了當前少量範圍內的操作，取代了在日誌中儲存的詳細操作。
To preserve the changes for an entire bulk load operation, extents that are marked as changed are also
stored in the log. As a result of only storing the final result of multiple operations, the log is typically
smaller and bulk operations can run faster.
Using this model can restore all data, but a disadvantage is that it is not possible to restore only part of
a backup, such as restoring to a specific mark.
為了阻止針對整個大批負荷操作的改變，被改變的範圍的標記也被儲存在日誌中。結果是僅儲存了多個操作的最終結果，日誌是明顯的較小和大批操作能快速的執行。
使用這種模型能還原所有資料，但它的一個缺點是，它不可能有的還原到備份的某一部分，比如還原到指定的標記位。