How do I ignore simple recovery mode databases?

[gkman]

Hello,

I have many sql transaction log backups partially completing. checking the detailed status I found:

"Operation inhibited by NetBackup for Microsoft SQL Server: Only a full or incremental database backup can be performed on database <StageCDR> because it uses the simple recovery model or has 'truncate log on checkpoint' set."

Obviously understanding that the database attempted to backup is in simple recovery model and therefor cannot be backed up.

our backup scripts look like:

OPERATION BACKUP
DATABASE $ALL
SQLHOST "<SERVER NAME>"
NBSERVER "<MASTER-SERVER>"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTTYPE TRXLOG
NUMBUFS 2
ENDOPER TRUE

is there a way to exclude all databases using simple recovery model?

[Michal_Mikulik1]

Hello,

with backup scripts, no.

However if you are on 7.7 (both Master Server and Client), you can switch to so-called intelligent MS SQL policies. They don't use local backup scripts and during transaction log backups, they are able to skip databases with Simple recovery model automatically.

Regards

Michal

[NBU35 · Accepted solution]

Hi, To exclude any DB from SQL DB backup by NetBackup for SQL agent , add following line in your .bch file.

EXCLUDE ''Database_name"

for example you want to exclude datababe name master then script should be like :

OPERATION BACKUP

DATABASE $ALL
EXCLUDE "master"

SQLHOST "<SERVER NAME>"

NBSERVER "<MASTER-SERVER>"

MAXTRANSFERSIZE 6

BLOCKSIZE 7

OBJECTTYPE TRXLOG

NUMBUFS 2

ENDOPER TRUE

[Michael_G_Ander]

You can EXCLUDE the simple mode database from your transaction log script by EXCLUDE "Database name" with one entry for each simple mode database.

Of course you have to verify with the DBA that the database should be in simple mode

[Michal_Mikulik1]

Hello,

the posts above are right, so there is a EXCLUDE keyword for backup scripts. However this option is static, so you must monitor&maintain list of these databases/exclusions when Recovery Model of any database changes. With SQL Intelligent policies, it is dynamic.

Regards

Michal

[Dave19]

Hello. The SQL Intelligent Policies are only scheduled from within NetBackup yes? The problem I have with that is it is a second scheduler that I need to coordinate. Heavy IO operations like backups, check databases, index defragmentation, warehouse ETL's etc can't run at the same time otherwise they can kill the server. I like the batch file option and seems to be working good so far in a SQL Agent job. But it would be really awesome if there was a "skip databases in simple recovery mode" for transaction log backups. This would save the manual administration of the EXCLUDE in the bch file.

[Mystre316]

You should still be able to schedule them outside of Netbackup regardless of how you are protecting them (instances, instance groups or batch files).

Also keep in mind that if your EXCLUDE list is longer than what you can backup, get your DBAs to specify what they want backedup instead of ALL DBs. Again, a lot of manual work is involved, but that would fall to the DBAs to remember that they need to edit scripts.

[Marianne]

You may want to submit a 'Request for Enhancement' as explained over here:

https://vox.veritas.com/t5/NetBackup/Need-a-script-option-for-log-backups-to-skip-databases-using-th...

[Dave19]

Hello. the problem with SQL intelligent policies is that the scheduler is controlled by NetBackup. Using dbbackex I can integrate it with everything else running on SQL, Backups, check DB, defragmenting, warehouse activity...all are heavy IO operations and should not be running at the same time. using DBBackexe I can easily integrate it into the nightly routines. Using the intelligent policies requires frequent montioring because its a separate schedule. Also some processes need to have a backup before proceeding so intelligent policies is much harder to use. Please make an option in the BCH file to skip simple mode databases.