Support for transaction log backup

[Dje_be]

Dears,

I'm looking for a proper solution to fine-tune SQL Server database transaction log backup.

Today, I have one policy per instance per server, dealing with $ALL databases such as:

OPERATION BACKUP
OBJECTTYPE TRXLOG
SQLHOST "serverName"
SQLINSTANCE "InstanceName"
DATABASE $ALL
BROWSECLIENT "servername"
NBSERVER "masterServerName"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
 

Issue is that the list of databases generated by the $ALL does not take into consideration database recovery model for which the clause should be recovery_model_desc <> 'SIMPLE' leading to NBU job status 1.

I wanted to use the bpstart_notify.policy.bat to generate the .bch on the fly using this query:

set nocount on

select 'OPERATION BACKUP
OBJECTTYPE TRXLOG
SQLHOST "' + case when charindex('\',@@servername) > 0 then substring(@@servername,1,charindex('\',@@servername) - 1) else @@servername end + '"
SQLINSTANCE "' + case when charindex('\',@@servername) > 0 then substring(@@servername,charindex('\',@@servername) + 1, len(@@servername)) else 'DEFAULT' end + '"
DATABASE "' + name + '"
BROWSECLIENT "' + case when charindex('\',@@servername) > 0 then substring(@@servername,1,charindex('\',@@servername) - 1) else @@servername end + '"
NBSERVER "masterServer"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE
'
from sys.databases
where recovery_model_desc <> 'SIMPLE'

combine with sqlcmd such as:

sqlcmd -Sservername\instanceName -E -i"C:\Netbackup\SQL\Scripts\generate_TRXLOG.sql" -o"C:\Netbackup\SQL\Scripts\DEFAULT_FULL_TRXLOG.bch" -h-1

But there is no support of bpstart_notify with DBBACKUP.

My solutions are to schedule this script out of NBU/NBU Policy but then I need to manage different scheduling and notification of error which adds complexity for a simple task.

Or to live with jobs returning partial backups with status 1.

Would there be a solution, available in 7.5.0.3 or later, allowing me to perform this whole in a consolidated way in NBU ?

Is there any plan to have this feature released in future NBU versions ?

Thank you very much in advance,

Best regards,

Jerome

[Marianne]

You can exclude these databases in the script, e.g:

EXCLUDE MASTER

Exctract from NBU for SQL Admin Guide:

EXCLUDE can be used in a batch file only if DATABASE $ALL is used.

[Dje_be]

Thank you for your suggestion.

Unfortunately it will not solve my problem to dynamically manage all databases transaction log backup as there can be one or more database to exclude and it might comes in a request to turn in FULL recovery model a database which is in simple recovery model for business reasons, which will then lead me also to modify my exclusion list.

The suggestion which would be the cleanest according to me is to make sure DBBACKUP exclude himself the list of databases in simple recovery model when the $ALL is combined to OBJECTTYPE TRXLOG for an MS-SQL server policy as anyway it makes no sense even to attempt to backup the transaction log of databases in simple recovery model.

Cheers,

Jerome