How to correctly back up and truncate SQL transaction logs using the NetBackup for SQL Server database extension

Description

The following information assumes that the SQL Server database option Truncate log on checkpoint isdisabled (SQL 7), or is set to full recovery mode (SQL 2000).

There are two ways to successfully back up SQL Server transaction logs and have them truncated. In both cases, the NetBackup batch files (or scripts) to back up and truncate the transaction logs are generated in the NetBackup Microsoft SQL Client GUI.  

From within the NetBackup Microsoft SQL Client GUI,select Actions | Backup |Transaction Logs. In the resulting window,select the databases that need to have transaction logs backed up and truncated. Next, select the Save for later execution option under the backup script.Then, click OK to save the batch file. When the batch file is opened, it will look like Script 1, which is shown below:

Script 1 (This script will back up and truncate the Northwind transaction log):

OPERATIONBACKUP
OBJECTTYPETRXLOG
DATABASE"Northwind"
MAXTRANSFERSIZE0
BLOCKSIZE0
DBMSMSSQL
DSN"veritas"
ENDOPERTRUE

Scripts can also be created to just back up the transaction log without truncating it, or only truncating it. From the NetBackup Microsoft SQL Client GUI, select Actions | Backup | Transaction Logs. In the window that appears, select the databases that need to have transaction logs backed up and truncated. Then, select the Save for later execution option under the backup script. Finally, go to the Transaction Log Backup Options, where it is possible to specify no truncation ( no_truncate)or only truncate ( truncate_only). Those two options combined into one script would look like Script 2, shown below:

Script2

OPERATIONBACKUP
TRXOPTIONNOTRUNC
OBJECTTYPETRXLOG
DATABASE"Northwind"
MAXTRANSFERSIZE0
BLOCKSIZE0
DBMSMSSQL
DSN"veritas"
ENDOPERTRUE

OPERATIONBACKUP
TRXOPTIONTRUNCONLY
OBJECTTYPETRXLOG
DATABASE"Northwind"
MAXTRANSFERSIZE0
BLOCKSIZE0
DBMSMSSQL
DSN"veritas"
ENDOPERTRUE

NOTES:

• Script1 does the same thing as Script 2. Script 2, however, demonstrates how NetBackup provides the options to just back up or just truncate the transaction log files, in addition to the option of backing up and truncating the logs.
• The script information for transaction log backups can be appended to a batch file that backs up databases, provided the database batch file is not using the $ALL option.
• The use of the DSN keyword is not required in versions of NetBackup newer than NetBackup 4.5 Maintenance Pack 1 (MP1).