How to Remedy a Backup Exception

[Chaplain_Doug]

Whenever I run a backup of a SQL instance (and its databases), I get the following exception:

V-79-57344-38728 - The SQL database 'OneDay' on virtual machine 'GNWeb' is configured to maintain transaction logs, but transaction log backups are not being performed. Not backing up the transaction log results in the log growing in size until it fills all available disk space. You should either schedule regular log backups or change the database to the simple recovery mode.

How can I remedy this situation?

[VJware · Accepted solution]

Have you looked at - http://www.symantec.com/docs/TECH137587 ?

And, this too - http://www.symantec.com/docs/TECH128944

[Chaplain_Doug]

VJWare:

Do you know how to turn off the transaction logging on a SQL database?  This too might solve the problem.

[VJware]

i don't think transaction logging can be completely disabled...would recommend you to setup log backups, if they havent been setup yet...

KB for backing up logs - http://www.symantec.com/docs/HOWTO24050

[Ken_Putnam]

Agree with VJWare

Even if you could disable Transaction Logging, why would you do this on a Business Critical machine.  And if the database is not business critical, why bother backing it up in the first place?

[Chaplain_Doug]

As you might gather, I am a little confused on this one.  I have been using Backup Exec 11.x on my other server that has SQL databases and I NEVER got this exceptioon.  It seems that even if I do a separate backup of the transaction log, I would still get this exception on my full backup.  My objective is:

• To NOT have the exception show up on the full backup job AND to have the SQL instance (and all databases therein) backed up by the job.

I am a jack of all trades and an expert in none, being the system admin, database programmer, network admin, web site developer, hardware specifier and setter-upper, financial software designer, etc.  I do my best to limp through these things until I have them working.  Then I leave them to themselves.  I pray that things will work out of the box, but find that to seldom be the case.  We are a Christian non-profit and got our Backup Exec 2010 R3 through a NP reseller.  By design it came with NO telephone support.  So if I don't get the fix here, I am up a tree.  Now that you know my situation, back to the problem . . .

The transaction log backup to which you refer . . . is that done to tape or disk?  How does this backup keep the log from getting larger and larger (as inferred by the exception message)?

I truly do appreciate the help you all have tired to give me here.  Thanks.

[Ken_Putnam]

This has been a severe complaint with me since v6.11 and SQL 6.0

If you do a database backup using the MSSQL tools, you get both database and tran logs

In BackupExec if you do a database backup you get ONLY the database.  So what you have to do is run a database backup followed by a second job that does a Transaction Log Backup/Purge Logs   If you make the second job APPEND, they will write to the same tape

[pkh]

The reason why you are not getting this warning on your other server is probably because the databases are set to a simple recovery mode, i.e. they are not using the transaction logs.

Before you can the recovery mode to simple, you should check with the application developer.  They might need to logs for some recovery purposes.

That said, if you are using transaction logs and you run the log backup job to truncate them, you would not get the log error message.

[Chaplain_Doug]

In reading documentation on this I found the following:

1.  This exception is new to Backup Exec 2010 R3.  It was not done in previous versions of Backup Exec.

2.  Backup Exec allows 11 backups without throwing this exception.  On the 12th backup if a log/truncate backup of the SQL database has not been done, the exception is thrown.

As soon as I did a "Log - Backup transaction log" backup of the SQL database (a backup option found under the Microsoft SQL settings on the backup job setup) the exception went away on the next backup.

My suggestion would be to schedule a Log backup every other Friday to run before the full backup.  I would have the log backup not eject the tape and have the full backup append to the tape.