SQL Full Backup does not include Transaction Logs

[Ross_Smith_2]

WTF??!!!

Can anybody give me a good reason why Veritas should not be hung out to dry for this?

BackupExec does not truncate transaction logs after a successful full backup of a SQL database, instead a second job needs to be scheduled to backup the transaction logs. Nowhere do they make this clear in the manual.

Ok, so if you're running SQL you should know this already and yes, I should have checked, but like a lot of network admins I wear a lot of different hats. SQL is just one of a multitude of things I have to look after.

What I'm really struggling to get my head around is why Exchange backups default to Full, including transaction logs? If it's possible for Exchange surely it's possible for SQL??

All I know is that it would make my life a whole lot simpler to just have one full backup job for our SQL databases instead of one differential job but two full ones.

Ross

[Renuka_-]

Hello,


1.You had written to us on 1/24/05:

All I know is that it would make my life a whole lot simpler to just have one full backup job for our SQL databases instead of one differential job but two full ones.


What exactly do you mean by this? You would prefer to have one full backup instead of what?? One differential or two full backups or both??


2.Please run a SQL backup and truncate the transaction logs via SQL. After this check the results with backup Exec.

3. While running the SQL backups using Backup Exec; please uncheck AOFO option if you have it.

4.We would also like to clarify that the trucation of transaction logs is done by SQL itself; after the backup job is complete Backup Exec will send a message to SQL to truncate its own transaction logs.

5.Please elaborate on the version of SQL you are using and the backup strategy being used.

NOTE : If we do not receive your reply within two business days, this post would be marked assumed answeredand would be moved toanswered questions pool.

[Ross_Smith_2]

I'll answer your points in order:

1.It seems you also have some confusion over the way BackupExec does full backups of SQL Server.

To perform a full backup, two separate jobs are needed:
a. Full backup of the database
b. Full backup of the transaction logs.

It would seem to make sense to have a backup option that carried out both steps. In fact, for Exchange Server (which has a similar storage method), you have exactly that - an option to do a full backup including logs.

2. Pointless response, irrelevant to my question. I assume you're talking about running a backup via SQL Enterprise Manager. Not interested, it can be done with BackupExec, it's just a pain.

3. Not using AOFO, was not mentioned in my message, irrelevant.

4. I'm aware the truncation is done by SQL, my point is that you need to run two different types of full backup in BackupExec before it signals to SQL that the logs can be cleared.

5. I'm using SQL 2000 and running weekly full backups with daily differentials. This means I run:

Weekly full backup job 1 - full backup of databases
Weekly full backup job 2 - full backup of logs
Daily differential backup - full backup of logs (no truncate)

[Ross_Smith_2]

PS. Just to point out why this is such a pain:

Our entire backup strategy is full backups at the weekend, daily differential backups, and we have our selection lists and policies working nicely like this.

Now I've found out that SQL needs two full backup jobs I've actually had to change my main backup from:

1 Selection list of all files & databases
1 Policy with 2 templates specifying weekly full & daily differential backups

to:

3 Selection lists:
- one for files
- one for databases with full logs
- one for databases with circular logging
3 Policies
- one with 2 templates for files running the weekly full & daily diff backups.
- one with 3 templates for databases with logs running weekly full backups of databases, weekly full backups of logs and daily differential backups of logs
- one with 1 template for databases with circular logging running weekly full backup of databases

Far, far more complex than before.

[Arjan_van_Doorn]

Hi Ross and Veritas support.

I'm having exact the same problem.
This and the fragmentation problem will cost Veritas customers. Unbelieveable you buy special SQL clients and get this... way to go Veritas!
We are definatly searching for an other solution...

[Studio_Two]

Hello,

We have been running SQL Backups for a few months now. I only noticed this issue when the Transaction Log became very large.

From the instructions, this is not very clear at all. I assumed "Full Database Backup" meant exactly that.

The first question I have is that (other than having a large transaction log), am I currently making an adequate overnight backup of my SQL Server?

TIA,
Stephen

[Hywel_Mallett]

A Full SQL backup is adequate for disaster recovery purposes. As you have seen, large transaction logs are side-effect of only doing full backups.

Not a direct answer to your question, but might be helpful: http://www.backupexecfaq.com/faq/concepts/backing-up-microsoft-sql-server.html