Backup Exec 2010 + SQL + Sharepoint DB backup

[woonks]

Hi,

I need to revisit the infamous "Transaction log backups are not being performed" alert. Current backup method uses SQL "Simple" recovery mode.

But in this mode the GRT mode doesnt work!! I had to restore 250GB just to retrieve a table content. This is not something I wanna do often and beats the purpose of GRT and getting a SQL agent in the first place....

So..... next option is create a separate job for a transaction log backup.

But I dont want to use a separate tape or schedule for this. I would like to daily run a automatic DB backup job followed by a transaction backup job together to a tape. 

I am under the impression that BE policy may achieve this, but i havent used a policy before. Anyone has any good idea on this?

Thanks.

[pkh]

A policy will not work.  A policy can only have one selection list, but the selection list for your DB backup and log backup will be different.  When you do your DB backup, you would also want to backup your master database.  The master database is always in simple recovery mode and there is no log to backup, hence it cannot be included in the selection list for the log backup.  You would have to create two jobs with different selection list for your DB and log backups.

I think the machine that have your SQL databases is a VM.  In this case, you would need 3 jobs

1) for the whole VM, if you are backing the machine up as a VM. 

2) DB backup

3) log backup.

See this document

http://www.symantec.com/docs/TECH137587

[woonks]

Can I confirm that in the case full backup of SQL full recovery mode, if i totally lost the transaction logs i can still recover back the database to the last full backup aka behave just like the simple recovery mode?

I am still torn between use BE2010 to manage the transaction logs or let SQL manage the logs....

If the above stands correct, I will definately use BE2010. But sadly, the backup jobs will still give a failed status. Well, one gotta give and take. 

[pkh]

You don't need the transaction logs if you are not doing point-in-time restores.  It is good to have them just in case you need to do a point-in-time restore.  Some applications may require you to have them.  If you take a full database backup, you can restore the database to the time of the backup.

You can use SQL to truncate the logs without backing them up, but this is a manual process.  It would be better to do everything with BE.  You can schedule your jobs to run accordingly.

One advantage of backing up your databases separately is that when you need to restore them, it is a simple restore.  With your VM backup, you got to stage the VM onto disk before you can restore a single database if the backup is to tape.

If you are relying on your VM backup as the primary backup, you can schedule your log backups less frequently like once a week.