cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Log Backup (Truncate), ADBO

davei
Level 4

Hi

I have a MSSQL server (2008 R2 Ent) with several instances and approximately 20 volumes delivered using guest-based ISCSI to some storage.

I back the databases up using SQL Agent (2014) and use ADBO off-host backup to perform these backups.  Works great.

I wish to configure a Backup Log+Truncate job to run through one of the instances on a scheduled basis, eg. each Sunday.  I am struggling to achieve this in the 2014 product:

  • I can't include it in the existing 'policy' (GFS), as the whole 'policy' uses off-host backup (no point truncating on a r/o snapshot right!)
  • I seem unable to create a stand-alone job to do a backup log+truncate job, as the first template in the 'policy' only allows a SQL job type of "Full" or "Full (Copy)", the option for Log backups is not there.

I like the look of the new "Automatic" job type for SQL log Backups, where DBs in simple recovery mode won't cause a log+truncate job to fail, so i can target at the instance level rather than manually keeping a list of DBs up to date in the selection list.

What is the recommended approach, and what to do others do, when wishing to do a log+truncate on some databases when off-host backup is used?

I'm using Backup Exec 2014.

Thanks.

3 REPLIES 3

VJware
Level 6
Employee Accredited Certified

Afaik, if snapshot/VSS is used for SQL backups, then only full backups are supported and log backups are not.

I guess, only way to run log backups would be to run non-snapshot backups, i.e. without ADBO in your case.

davei
Level 4

Thanks for the reply.

Disappointing as in BE 2010 we could just run a stand-alone job separate to the ADBO job, to run a full log backup & truncate.

Is there a reason why this is not doable in BE 2014?

For clarity, when trying to configure a stand-alone job to do this, i do the following:

  1. Right-click SQL server in BE2014, select Backup and the Backup To Disk
  2. In Selection list, tick the SQL instance in question.
  3. Edit the templates (default is 1x Full and 1x Incremental).

Now if i go to the "Microsoft SQL" section of the Backup Options, under the 'Full' template, my available Backup Methods are Full, and Full Copy. 

Under the Incremental template, I get more choices and can choose Log Backup etc.

I already back up the database using ADBO (important for my environment, the SQL server is a VM and off-host backups of these ISCSI volumes are important operationally).  So i do not want to do *another* full backup, especially one that is not off-host.  I just want to truncate my SQL logs.

I know any Log backup would not be off-host, i can live with that.

Perhaps i am misunderstanding something fundamental about the process.

I can't be the only one to do off-host SQL backups, and still want to truncate my logs?

I know i can't do it all in one job, that is fine.

 

 

 

SSR_7767
Level 4

Hi Davei,

An incremental or log backup cannot be configured in Backup Exec 2012 and above, without a full database backup.

May I know what is the size of your database?

We can run a full back up only once without using snapshot technology and configure Incrementals to run on a daily basis which would truncate the logs.