Ok after much investigation, and some discussion with an EMC Avamar tech, this is what I found out....
The entire discussion assumes a database with the Recovery Model option set to Full or Bulk Logged.
With SQL 2005 there are 2 methods to truncate the log through Avamar. The first is to perform a full database backup, by going into more options, choose Full, then go to advanced options, and choose to truncate the log. The other method, which I believe is the recommended method of backing up databases in SQL 2005 or 2008, is to perform a standard Full backup (no log truncation), then to perform a log backup as a separate job. A log backup is known as an incremental SQL backup in Avamar.
The reason that the second method is recommended, is becuase when you truncate a log without actually backing it up (which is what the first method does), you are no longer able to do a point in time recovery. And because of this, truncating logs when doing a full backup in sql 2008 is not supported or allowed.
One other thing to note is that the more often you backup the log in between your full backups, the less you are likely to have the log grow out of hand. It also means you have more granularity in your point in time restore.
Another caveat with point in time restores with Avamar, is that older clients do not have this option for SQL. I am currently running version 5.0.1.32 and am unable to do point in time restores of SQL databases. But apparently later versions (not sure exactly which ones) do support this feature.
This is a very simplistic overview of the log backup process, but hopefully it helps someone. And please correct me if anything I'm saying is incorrect.
I have the exact same question but we are on SQL 2005 server instead. I am also doing FULL backup type and Truncate log set to default and according to the Avamar SQL guide this does no log truncation if the backup type is set to FULL. I am wondering how are other Avamar users doing their SQL backup and especially how are they truncating the log? Are you all setting the truncate database log option to "Perform Log Truncation" when performing FULL backup type?
If you can have Avamar automatically do an incremental backup after a full backup, then that will certainly truncate the log. Just keep in mind that the log file will grow again until the next full-then-incremental backup. Depending on the size of your log disk, and how often you run the full-then-incremental backup, this may not be enough. In that case, another full-then-incremental, or incremental backup will need to be done. However because Avamar is so good with dedupes, you can probably get away with full-then-incremental backups every hour during your backup window.
That still leaves the problem of point in time restores during your Avamar black-out and maintenance windows. Since you can't (or shouldn't) do backups during those times, how are you supposed to restore to that point in time (for really critical databases)? So I'm going to play around with doing full backups using Avamar, then performing Log backups (say every 15 minutes or every hour) through the SQL Maintenance Plans on the SQL server itself. Just not sure if I can use Avamar to restore the Full backup using the NORESTORE option, then apply the log backups from my maintenance plans. Wish me luck!
Thanks for the clarification as it was very helpful but there seems to be one other option that is selected by default and that option is "Force Incremental backup" and the definition if you hover the mouse point over it says "Force Incremental backup after Full backup" so it would seem that a separate "Incremental" backup to backup the transaction log and truncate it is not needed. BTW I'm on Avamar version 5.0.2-41 which is 5.0 SP2.
I think the default setting for my version of Avamar is running a "Full" backup and then another "Incremental" backup because I see both under the Restore window for the various SQL servers. I just need to check with my DBA to verify that the transaction logs are being truncated as I'm not sure how to verify that via Avamar or within SQL.
candal02
22 Posts
0
September 14th, 2010 10:00
Ok after much investigation, and some discussion with an EMC Avamar tech, this is what I found out....
The entire discussion assumes a database with the Recovery Model option set to Full or Bulk Logged.
With SQL 2005 there are 2 methods to truncate the log through Avamar. The first is to perform a full database backup, by going into more options, choose Full, then go to advanced options, and choose to truncate the log. The other method, which I believe is the recommended method of backing up databases in SQL 2005 or 2008, is to perform a standard Full backup (no log truncation), then to perform a log backup as a separate job. A log backup is known as an incremental SQL backup in Avamar.
The reason that the second method is recommended, is becuase when you truncate a log without actually backing it up (which is what the first method does), you are no longer able to do a point in time recovery. And because of this, truncating logs when doing a full backup in sql 2008 is not supported or allowed.
One other thing to note is that the more often you backup the log in between your full backups, the less you are likely to have the log grow out of hand. It also means you have more granularity in your point in time restore.
Another caveat with point in time restores with Avamar, is that older clients do not have this option for SQL. I am currently running version 5.0.1.32 and am unable to do point in time restores of SQL databases. But apparently later versions (not sure exactly which ones) do support this feature.
This is a very simplistic overview of the log backup process, but hopefully it helps someone. And please correct me if anything I'm saying is incorrect.
fl1
89 Posts
0
September 14th, 2010 08:00
I have the exact same question but we are on SQL 2005 server instead. I am also doing FULL backup type and Truncate log set to default and according to the Avamar SQL guide this does no log truncation if the backup type is set to FULL. I am wondering how are other Avamar users doing their SQL backup and especially how are they truncating the log? Are you all setting the truncate database log option to "Perform Log Truncation" when performing FULL backup type?
candal02
22 Posts
0
September 14th, 2010 11:00
If you can have Avamar automatically do an incremental backup after a full backup, then that will certainly truncate the log. Just keep in mind that the log file will grow again until the next full-then-incremental backup. Depending on the size of your log disk, and how often you run the full-then-incremental backup, this may not be enough. In that case, another full-then-incremental, or incremental backup will need to be done. However because Avamar is so good with dedupes, you can probably get away with full-then-incremental backups every hour during your backup window.
That still leaves the problem of point in time restores during your Avamar black-out and maintenance windows. Since you can't (or shouldn't) do backups during those times, how are you supposed to restore to that point in time (for really critical databases)? So I'm going to play around with doing full backups using Avamar, then performing Log backups (say every 15 minutes or every hour) through the SQL Maintenance Plans on the SQL server itself. Just not sure if I can use Avamar to restore the Full backup using the NORESTORE option, then apply the log backups from my maintenance plans. Wish me luck!
fl1
89 Posts
1
September 14th, 2010 11:00
Thanks for the clarification as it was very helpful but there seems to be one other option that is selected by default and that option is "Force Incremental backup" and the definition if you hover the mouse point over it says "Force Incremental backup after Full backup" so it would seem that a separate "Incremental" backup to backup the transaction log and truncate it is not needed. BTW I'm on Avamar version 5.0.2-41 which is 5.0 SP2.
I think the default setting for my version of Avamar is running a "Full" backup and then another "Incremental" backup because I see both under the Restore window for the various SQL servers. I just need to check with my DBA to verify that the transaction logs are being truncated as I'm not sure how to verify that via Avamar or within SQL.
tatan1
6 Posts
0
January 26th, 2016 01:00
HI Candal02,
Any updates on this issue?Did you tried the incremental backup every hour in Avamar?thanks
ionthegeek
2 Intern
•
2K Posts
0
January 26th, 2016 06:00
This post is from six years ago. A lot has changed since then.