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.
为了阻止针对整个大批负荷操作的改变，被改变的范围的标记也被存储在日志中。结果是仅存储了多个操作的最终结果，日志是明显的较小和大批操作能快速的运行。
使用这种模型能还原所有数据，但它的一个缺点是，它不可能有的还原到备份的某一部分，比如还原到指定的标记位。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-607663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-607663/