none
Bulk Recovery Model RRS feed

  • Question

  • Hello,

         What are the appropriate situations to assign DATABASE as Bulk recovery model. Thanks in advance.

    Regards,

    Krishna

    Friday, December 7, 2012 9:46 AM

Answers

  • The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

    Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

    However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

    Here are some reasons why you may choose this recovery model:

    • Data is critical, but you do not want to log large bulk operations
    • Bulk operations are done at different times versus normal processing.
    • You still want to be able to recover to a point in time

    Type of backups you can run when the data is in the "Simple" recovery model:

    • Complete backups
    • Differential backups
    • File and/or Filegroup backups
    • Partial backups
    • Copy-Only backups
    • Transaction log backups

    http://msdn.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

    http://www.mssqltips.com/sqlservertutorial/5/sql-server-bulklogged-recovery-model/

    • Proposed as answer by Ramesh Babu Vavilla Friday, December 7, 2012 10:22 AM
    • Marked as answer by kriuk Saturday, December 8, 2012 12:21 AM
    Friday, December 7, 2012 9:52 AM
  • Hello,

    Please refer the below one when to use FULL/SIMPLE/BULK recovery model

    Choosing the Recovery Model for a Database and when to use
    http://msdn.microsoft.com/en-us/library/ms175987%28v=SQL.105%29.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    • Proposed as answer by Ramesh Babu Vavilla Friday, December 7, 2012 10:22 AM
    • Marked as answer by kriuk Saturday, December 8, 2012 12:21 AM
    Friday, December 7, 2012 10:05 AM

All replies

  • The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.

    Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

    However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

    Here are some reasons why you may choose this recovery model:

    • Data is critical, but you do not want to log large bulk operations
    • Bulk operations are done at different times versus normal processing.
    • You still want to be able to recover to a point in time

    Type of backups you can run when the data is in the "Simple" recovery model:

    • Complete backups
    • Differential backups
    • File and/or Filegroup backups
    • Partial backups
    • Copy-Only backups
    • Transaction log backups

    http://msdn.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

    http://www.mssqltips.com/sqlservertutorial/5/sql-server-bulklogged-recovery-model/

    • Proposed as answer by Ramesh Babu Vavilla Friday, December 7, 2012 10:22 AM
    • Marked as answer by kriuk Saturday, December 8, 2012 12:21 AM
    Friday, December 7, 2012 9:52 AM
  • Hello,

    Please refer the below one when to use FULL/SIMPLE/BULK recovery model

    Choosing the Recovery Model for a Database and when to use
    http://msdn.microsoft.com/en-us/library/ms175987%28v=SQL.105%29.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    • Proposed as answer by Ramesh Babu Vavilla Friday, December 7, 2012 10:22 AM
    • Marked as answer by kriuk Saturday, December 8, 2012 12:21 AM
    Friday, December 7, 2012 10:05 AM