Please enter search query.
Search <product_name> all support & community content...
How to correctly back up and truncate SQL Server transaction logs using the NetBackup for Microsoft SQL Server database agent
Article: 100017073
Last Published: 2013-10-24
Ratings: 1 0
Product(s): NetBackup & Alta Data Protection
Problem
How to correctly back up and truncate SQL Server transaction logs using the NetBackup for Microsoft SQL Server database agent
Solution
The following information assumes that the SQL Server database option
Truncate log on checkpoint is disabled (SQL 7), or is set to full recovery mode (SQL 2000 or later).
There are two ways to successfully back up Microsoft SQL Server transaction logs and have them truncated. Both methods use NetBackup batch files (or scripts) to back up and truncate the transaction logs. The batch files are generated in the NetBackup for MS SQL Client program.
From within the NetBackup MS SQL Client program, select File | Backup SQL Server objects. In the Backup SQL Server Objects dialog, select the databases whose transaction logs are to be backed up and truncated. Under the Type of Backup section, select the transaction log option. Then select Backup and truncate transaction log from the Backup and truncate transaction log section. Next, select the Save option under the Backup script section. Then, click Backup to save the batch file. When the batch file is opened, it will look similar to Script 1, which is shown below:
Script 1 (This script will back up and truncate the Northwind transaction log.)
Scripts can also be created to just back up the transaction log without truncating it, or only truncating it (SQL Server 7 and SQL Server 2000 only). From the NetBackup MS SQL Client program, select File | Backup SQL Server objects. In the Backup SQL Server Objects dialog, select the databases whose transaction logs are to be backed up and truncated. Under the Type of Backup section, select the transaction log option. Then select Backup transaction log, but do not truncate it from the Backup and truncate transaction log section. Next, select the Save option under the Backup script section. Then, click Backup to save the script. Repeat this, only select Truncate transaction log, but don't back it up, and save the script to the same file. Those two options combined into one script would look like Script 2, shown below:
Script 2:
NOTES:
There are two ways to successfully back up Microsoft SQL Server transaction logs and have them truncated. Both methods use NetBackup batch files (or scripts) to back up and truncate the transaction logs. The batch files are generated in the NetBackup for MS SQL Client program.
From within the NetBackup MS SQL Client program, select File | Backup SQL Server objects. In the Backup SQL Server Objects dialog, select the databases whose transaction logs are to be backed up and truncated. Under the Type of Backup section, select the transaction log option. Then select Backup and truncate transaction log from the Backup and truncate transaction log section. Next, select the Save option under the Backup script section. Then, click Backup to save the batch file. When the batch file is opened, it will look similar to Script 1, which is shown below:
Script 1 (This script will back up and truncate the Northwind transaction log.)
OPERATION BACKUP
DATABASE "Northwind"
SQLHOST "SERVER_1"
SQLINSTANCE "DEFAULT"
NBSERVER "SERVER_1"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
OBJECTTYPE TRXLOG
ENDOPER TRUE
Scripts can also be created to just back up the transaction log without truncating it, or only truncating it (SQL Server 7 and SQL Server 2000 only). From the NetBackup MS SQL Client program, select File | Backup SQL Server objects. In the Backup SQL Server Objects dialog, select the databases whose transaction logs are to be backed up and truncated. Under the Type of Backup section, select the transaction log option. Then select Backup transaction log, but do not truncate it from the Backup and truncate transaction log section. Next, select the Save option under the Backup script section. Then, click Backup to save the script. Repeat this, only select Truncate transaction log, but don't back it up, and save the script to the same file. Those two options combined into one script would look like Script 2, shown below:
Script 2:
OPERATION BACKUP
TRXOPTION NOTRUNC
DATABASE "Northwind"
SQLHOST "SERVER_1"
SQLINSTANCE "DEFAULT"
NBSERVER "SERVER_1"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
OBJECTTYPE TRXLOG
ENDOPER TRUE
OPERATION BACKUP
TRXOPTION TRUNCONLY
DATABASE "Northwind"
SQLHOST "SERVER_1"
SQLINSTANCE "DEFAULT"
NBSERVER "SERVER_1"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
OBJECTTYPE TRXLOG
ENDOPER TRUE
NOTES:
- Script 1 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.
Was this content helpful?
Translated Content
Please note that this document is a translation from English, and may have been machine-translated. It is possible that updates have been made to the original version after this document was translated and published. Veritas does not guarantee the accuracy regarding the completeness of the translation. You may also refer to the English Version of this knowledge base article for up-to-date information.