Video Screencast Help

BE2012 and SQL DB using Full Recovery model

Created: 24 Jul 2012 | 1 comment
TheTallOne's picture
3 Agree
0 Disagree
+3 3 Votes
Login to vote

As many have posted in the forums, BE2012 will report errors if the SQL DB you have selected for a SQL Incremental Log backup is not using the Full Recovery model.  They have also posted that when the Incremental job with the SQL Log method option defined runs (which most people do very frequently) that it will do an Incremental backup of all of the other resources that have been selected (like the System State, which always runs as a Full backup).

While there are separate Exclusions that can be defined for each of the Incremental jobs (in this case you would excluded Every SQL DB except for the ones that are using the Full recovery model), this is not very clean and it will not address when new DBs are added that are using the Simple recovery model and have yet to be added to the exclusions.

I would like to suggest that BE2012 offer the ability to Exclude all other non-SQL Full recovery objects for that job when the SQL Incremental method is set to the Log method.  This would normally be a grayed out option titled “Exclude all other types of objects” that would only be available when the SQL Log method is selected.

Example:  The user wants to do Weekly Full, Daily Incremental and SQL Logs every 2 hours.  The user would be looking at a newly added server and select the Backup tool, which automatically selects all of the resources on that server.  The user knows that they have a Business Critical SQL DB using the Full Recovery model, as such they would add a Second Incremental job and rename it SQL.Logs.  The user would then change the SQL options of the SQL.Logs incremental job from Differential (the default) to Logs.  Doing this would un-gray the option “Exclude all other types of objects”, which the user would now select.

When the Full backup job runs it will do as it is expected.  After this the SQL.Logs incremental job will now be able to run every 2 hours.  When it does it will only target the SQL DBs that are using the Full Recovery model, as the Logs backup option is only valid for DBs using this model.  This would be an improvement over what happens now, which is the job will report an Exception error and the job also targets the File and System State resources as well.  When the Incremental Job with the SQL Incremental method of Differential runs on a daily basis it will operate as expected, covering the SQL DBs and the selected file resources.

Comments 1 CommentJump to latest comment

Jason Beebe's picture

I would like to second this suggestion.  It seems to make all kinds of sense.

I currently cannot figure out how to exclude all local drives, System State and Utility partition for my SQL Logs backup, so I just don't run one (not ideal).  Is there any way to do this?

Login to vote