October 22, 2014 at 1:11 pm
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
October 23, 2014 at 10:36 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2014 at 11:08 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply