Bulk-Logging Recovery Model

  • I've never used bulk-logging as a recovery model. For my reading, I gather it is more of a short-time process; switch to bulk-logging, perform some intensive db activities, and then switch back to Full or Simple recovery model. I'm trying to write code to check that all the databases on my server have been backed up, and I'm not sure whether I've coded the bulk-logging scenario correctly.

    My code looks like this:

    select *

    from master.sys.databases db

    left outer join msdb.dbo.backupset bus

    on db.name = bus.database_name

    and ((db.recovery_model = 1 and bus.type in ('D','I','L'))

    or (db.recovery_model = 2 and bus.type = 'L')

    or (db.recovery_model = 3 and bus.type = 'D'))

    The intent of the code is to eliminate records that have a type that is inconsistent with the recovery model. So if a database is changed from Full to Simple recovery, Differential and Log backup records are removed since they don't apply. But if a database is in Bulk-Logging, should I also be looking for Full backup records or just Log backups as it is currently coded? I don't expect that my db's would ever be in Bulk-Logging for more than a short period, but I'd like to cover my bases correctly.

    Thanks for any help/suggestions -- RMc

  • I think you should read the BOL article on Recovery Models, http://msdn.microsoft.com/en-us/library/ms189275(v=sql.90).aspx because some of your assumptions are incorrect. For example a database in SIMPLE recovery can have differential backups applied and the BULK_LOGGED recovery model can and should have Log backups, you just lose the ability to do a point in time recovery, you can still recover to the end of the log backup. So I'd treat FULL and BULK_LOGGED the same and just eliminate log backups for SIMPLE.

  • richardm-1037631 (10/22/2014)


    switch to bulk-logging, perform some intensive db activities, and then switch back to Full or Simple recovery model.

    Switch to bulk-logged, perform some bulk inserts or index rebuilds and then switch back to full.

    There is no point in ever switching from simple to bulk-logged recovery

    So if a database is changed from Full to Simple recovery, Differential and Log backup records are removed since they don't apply.

    You absolutely can take differential backups of a DB in simple recovery. Simple means no log backups

    But if a database is in Bulk-Logging, should I also be looking for Full backup records or just Log backups as it is currently coded?

    Bulk-logged is the same as full for the purposes of backups, so full, differential and log.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

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