Post reply

Recovery Model

  • July 11, 2008 at 4:38 am

    #381594

    Hi all,

    Am new to SQl Server.I want to know the difference between full recovery and bulk logged recovery.Although i gone through various documents i have no clear idea on that.

    In short, i want to know that does bulk logged recovery model only includes the minimal logging options like bulk insert,select into and create index?whether point in time recovery can be performed with bulk logged recovery model?

    What about the full recovery model?Does it includes or excludes the minimal logging statements?

    Thanks in advance.

  • July 11, 2008 at 7:03 am

    #841448

    Hi Sudarram,

    Full Recovery Model fully logs all transactions so the Bulk-Logged Recovery Model minimally logs bulk operations! Bulk-Logged Recovery Model works almost in same manner as Full Recovery Model but the only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical known as minimal logging.

    check BOL for further information:

    http://msdn.microsoft.com/en-us/library/ms190692.aspx

    hope it helps!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • July 15, 2008 at 12:54 am

    #842838

    full recovery model ---mdf,ldf

    bulk logged ---mdf ,1/2 ldf(partial )

    they work in the same way but for bulk operations there is bulk logged recovery model.

    this reads BULK CHANGE MAPS and identify the extents that modified and include them in log backup thus totally recovers the transactions

  • July 15, 2008 at 8:09 am

    #843127

    Hi,

    Here is a brief explanation:

    The Full Recovery model uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files is damaged, media recovery can restore all committed transactions. In-process transactions are rolled back.

    Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. To guarantee this degree of recoverability, all operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.

    The backup strategy for full recovery consists of:

    • Database backups.

    • Differential backups (optional).

    • Transaction log backups.

    • Full and bulk-logged recovery are similar and many users of the Full Recovery model will use the Bulk-Logged model on occasion.

    The Bulk-Logged Recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations. These operations are minimally logged:

    • SELECT INTO.

    • Bulk load operations (bcp and BULK INSERT).

    • CREATE INDEX (including indexed views).

    • text and image operations (WRITETEXT and UPDATETEXT).

    In a Bulk-Logged Recovery model, the data loss exposure for these bulk copy operations is greater than in the Full Recovery model. While the bulk copy operations are fully logged under the Full Recovery model, they are minimally logged and cannot be controlled on an operation-by-operation basis under the Bulk-Logged Recovery model. Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually.

    In addition, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported.

    It is not necessary to perform a full database backup after bulk copy operations complete under the Bulk-Logged Recovery model. Transaction log backups under this model capture both the log and the results of any bulk operations performed since the last backup.

    The backup strategy for bulk-logged recovery consists of:

    • Database backups.

    • Differential backups (optional).

    • Log backups.

    Backing up a log that contains bulk-logged operations requires access to all data files in the database. If the data files are not accessible, the final transaction log cannot be backed up and all committed operations in that log will be lost.

    Hope this explains your doubts:P

  • July 15, 2008 at 8:27 am

    #843145

    Rinu's explanation is the best.

    The advice is to run with full mode, unless you need to run a bulk operation, in which case, you can switch (online) to bulk mode, and then back to full mode after the bulk operation.

    The advantages for bulk mode are for large operations. If you bulk in 5,000 rows,I'm not sure I'd bother switching. This is more for 100,000s of rows or millions of rows.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 15, 2008 at 10:27 am

    #843232

    Thank you all for your explanations.

  • July 16, 2008 at 8:08 am

    #843791

    Just a little side note: A rebuild index is a bulk-logged operation. I had a situation where I was rebuilding a massive index over the weekend and the log kept filling up the disk. So I placed a step in front of the job to change the DB to "bulk-logged" and then when the rebuild index was done I had another step to change it back to "full". Tran backups were every 15 minutes, so PiT recovery was not an issue.

    Tim White

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply