Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Allow Netbackup SQL Agent to do Transaction Log backups and a Full or Incr. backup at the same time

Created: 24 Aug 2011 | 9 comments
tperez's picture
4 Agree
1 Disagree
+3 5 Votes
Login to vote

In Netbackup 6.5.6 the Netbackup SQL Agent was able to backup the transaction logs and a full or incremental backup at the same time.  However, in Netbackup 7.1 there was a code change that now prevents this.

This is a critical issue to Best Buy in the new 7.1 environment using the SQL Agent when trying to have a reliable point in time recovery.

Testing has shown that even running transaction log backups to disk will still causes the SQL Full or Incr backups to fail if they start after the transaction log backup to disk.

This is forcing us to use a 6.5.6 Netbackup agent in a 7.1 backup environment.  There are concerns of compatibility with this mixed environment.

Pleaes refer to case 415-127-860

Comments 9 CommentsJump to latest comment

austin_lazanowski's picture

A 6.x client is completely backward compatible with a 7.x environment. Rule of thumb is 1 major version backward compatible. The issue I could see where transaction logs being backed up during the Full or incremental backup would be that a full or incremental truncates the logs after completion. While I don't see any issue with the premise, I understand that it could cause issues if during the transaction log backups, the full or incremental backup completed and then truncated the transactions before the tlog backup is complete.

-Austin Lazanowski

"Backups are always too expensive until you needed them."

0
Login to vote
symse23's picture

Actually, a full or incremental backup does not truncate the transaction logs. A full back and a incremental only place a checkpoint in the transaction log so that sql knows where to replay the logs from on recovery.

0
Login to vote
austin_lazanowski's picture

Interesting, I did not know that. I am curious then, how do DBAs manage transaction log growth if not by having them truncate to a last Full or incremental? Is this something they do via a script?

-Austin Lazanowski

"Backups are always too expensive until you needed them."

0
Login to vote
Michael G Andersen's picture

We are having the same issue as tperez, that after upgrading from 7.0 to 7.1.0.2 our SQL backups has started to fail when the full and transaction log is running at the same time. We have requirement to our SQL log backup to run with 10 and 15 minutes frequency.

And have created case 417-646-505 on the issue have refered to tperez's case

0
Login to vote
Dyneshia's picture

To truncate the transaction logs you must do a transaction log backup. 

From the SQL sysadmin guide :

Full Backup - The database, including all of its component files are backed up as a
single image. The log file is included in a full database backup.

Note: The transaction log is not automatically truncated following a
full backup. Thus a common practice to preserve disk space is to
manually truncate the transaction log following a successful full
backup.

Transaction log backups are only available for the full and bulk-load
recovery options. In this operation, the inactive portion of the
transaction log is backed up. Four options are available when you
select transaction log backup:

■ Back up and truncate transaction log.

■ Back up transaction log, but do not truncate it.

■ Truncate the transaction log, but do not back it up. (SQL Server 2000 only)

■ Back up and restore tail log. (SQL Server 2005 only)

The third option is not a backup at all. It only provides a convenient
way to truncate the transaction log for you. It is typically used after
a full database backup. The last option is a backup but it does not
create a permanent backup image. A typical use of this type of
transaction log backup is: after a page-level restore when the database
is recovered, but all of the filegroups have not been brought back on
line. By backing up and recovering the tail end of the log, SQL Server
is able to bring the database back to a usable state.

+1
Login to vote
nathanmike's picture

@all,

Indeed, i am integrating netbackup 7.5.0.4 and I have create three policies for each mssql instances:

- an hourly (every 3h) FULL transaction log policy (excluding system database from .bch script)

- a weekly FULL databases (sys/user) policy

- a daily Differential/Incremental policy (excluding system database from .bch script)

You can also manage a specific policy for system database only

Nathan Mike

LPIC-1 and CLA-11

Symantec Registered Partner

nathan.mike@edotcom.be

www.edotcom.be&a

0
Login to vote
tarmizi's picture

Hi, 

I've got some question where after backing up the transaction log, it doesn't clear/truncate the transaction log. Now my customer disk space is low and need a way to prevent it form hanging the SQL. Have any of you guys know how to deal with it?

Thank you

0
Login to vote
nathanmike's picture

@tarimzi,

When you do transaction log backup, in the transaction log backup options (from netbackup mssql client GUI) you have options to:

1. Back up and truncate transaction log

2. Back up transaction log, but do not truncate it

If you need to truncate trasaction log, you must choose option 2 which will generate the following parameter in your .bch file (see the one in bold):

OPERATION BACKUP
DATABASE "$ALL"
EXCLUDE "master"
EXCLUDE "model"
EXCLUDE "msdb"
SQLHOST "SRVSQL"
SQLINSTANCE "TEST"
NBSERVER "MASTER.EXAMPLE.COM"
BROWSECLIENT "srvsql.example.com"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTTYPE TRXLOG
NUMBUFS 2
ENDOPER TRUE

=> for sure, your transaction log will be truncate. System database (master, model and msdb) are exclude because they are in simple recovery mode and cannot be include in such policy otherwize your backup will end partially with status 1 (or 2 sometimes).

If you do not need to truncate trasaction log, you must choose option 1 which will generate the following parameter in your .bch file (see the one in bold):

OPERATION BACKUP
DATABASE "$ALL"
EXCLUDE "master"
EXCLUDE "model"
EXCLUDE "msdb"
SQLHOST "SRVSQL"
SQLINSTANCE "TEST"
NBSERVER "MASTER.EXAMPLE.COM"
BROWSECLIENT "srvsql.example.com"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
OBJECTTYPE TRXLOG
TRXOPTION NOTRUNC

NUMBUFS 2
ENDOPER TRUE
 

=> transaction log will not be truncated (NOTRUNC)

Please check with your Customer the above and let us know.

I have more than 25 backup policies for transaction log backup based on the above and it works perfectly.

transaction log are truncated and no disk space issue ;-)

Nathan Mike

LPIC-1 and CLA-11

Symantec Registered Partner

nathan.mike@edotcom.be

www.edotcom.be&a

+1
Login to vote
Dyneshia's picture

Please start a new thread for the issue.

0
Login to vote