Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

BE2012 - Schedule SQL Transaction Log backup?

Created: 28 Mar 2013 | 17 comments

...and while I'm on a roll.

Symantec, why can you not schedule a SQL transaction log backup? Only way I have found to run it is as a One Off Job. Yes, you can run this job whenever you like, but only manually. Why - for the love of bits - can you not schedule it?

Edward

Operating Systems:

Comments 17 CommentsJump to latest comment

pkh's picture

You can schedule a transaction log backup.

1) Edit your job.

BE 2012 - edit job properties.png

2) add an incremental job

BE 2012 - add job.png

3) Go to the Microsoft SQL section and change the incremental job to a log backup

BE 2012 - SQL log backup.png

Edward Lowy's picture

Thanks - I have a job setup to do just this, however this does not truncate the log, so that just grows and grows. You can run a log backup which does truncate the log, but only as a one off job.

I appreciate you taking the time to reply, many thanks

Edward

VJware's picture

As suggested earlier, the transaction log backup can be scheduled.

However, do note running a transaction log backup does NOT affect the size of the physical log file. Only the logical log files are committed. If you want to reduce the size the of the physical log file, consider using the "Shrink" command.

Edward Lowy's picture

Perhaps I didn't explain myself properly. Why can I not schedule the job which backs up and truncates the SQL logs?

VJware's picture

You can as suggested earlier by pkh.

Are you able to commit the transactions using SQL or rather how did you check if the transation logs were not being truncated ?

Edward Lowy's picture

I think I am (a) not well versed in SQL & (b) confused! Thank you for your patience.

I think my issue is arising because I am backing up SQL instances witin a full Drive C backup. That backup of course is not configured to backup logs. I have removed SQL instances from this backup as I have a separate SQL backup (including logs) scheduled as another job. 

I think this will sort me out!

Kind regards

Edward

pkh's picture

When you do a log backup in BE 2012, it will truncate the log.

Edward Lowy's picture

Thanks for your advice, much appreciated. I can't help feeling that BE2012 should make backing up SQL a little easier. As I understand it at the moment, to backup SQL I need to :

(a) have one job for databases that are set to Simple

(b) a different job - with three stages - to back up databases that are set to Full

This allows for the possibility of having the wrong backup settings for a particular database, or missing a database altogether. There is a certain simplicity in just being able to tick the "SQL Server Instances" in the selection criteria and anticpating that the data will be backed up and more importantly of course can be correctly restored.

Our "shop" is quite small, not very techie, but it is amazing how many SQL instances we have - anti virus, spam, Jetadmin etc, not to mention bigger beasts like Autodesk. I doubt we are alone in having SQL backup issues, I think BE could be a lot less geeky and a lot more user friendly.

Kind regards

Edward

Jaydeep S's picture

Hello Edward,

I wish the SQL backups could be that simple from BE. The main limitation is as you rightly pointed out 'Recovery Model'

While backing up from backup exec or any 3rd party software for that matter, a single job can only perform a backup using a single backup method (Either Full, Incremental or Differential). If it makes job easier, you could select all the SQL Instances in the same job (Full-Incremental) and run it. However, you would get a warning for the databases running Simple recovery model while running the Incremental job. This is only a warning and not an error and can be ignored if you rightly know what you are doing.

Haviing said this, the recommendation to make 2 backup jobs is because those databases with Simple Recovery model will not be backed up everytime you run a Incremental job and would need to be protected seperatly (Step a in your post). This is the main reason for the complexity.

Edward Lowy's picture

Thanks - but "Smart Software" would analyse what needs to be backed up and then run the appropriate jobs!

That isn't complicated!

CraigV's picture

+1...to mark the OP down because of some sort of frustration is seriously lame!

Alternative ways to access Backup Exec Technical Support:

https://www-secure.symantec.com/connect/blogs/alte...

CraigV's picture

...point proven. Please PM me to explain (if you're able too) why the OP got a -1, and why I got 1 for defending him!

Alternative ways to access Backup Exec Technical Support:

https://www-secure.symantec.com/connect/blogs/alte...

Edward Lowy's picture

Hi Craig - are you addressing me? I haven't marked anyone or anything up or down. I am new to this Forum and do not know enough to pass judgment, positive or negative, on anyone or anything.

Or are you saying someone has marked me up or down? I can't see anything, votewise!

Kind regards

Edward

CraigV's picture

Hi Edward,

No, addressing the person who marked you down for your 1 post...I can see the -1. So I gave you a thumb's up (in the process the same lame person marked me down)...you don't get a point taken off for the -1, but it is a cheap shot which I "rectified".

I just find it a bit ridiculous when you as the OP with a legitimate problem get a -1, which is why I asked the person who did so to PM me...nothing yet, nor do I expect that.

Thanks!

Alternative ways to access Backup Exec Technical Support:

https://www-secure.symantec.com/connect/blogs/alte...

Edward Lowy's picture

Ah ok, many thanks. I have broad shoulders and generally have tried to find a solution before wasting people's time. There do seem to be quite a few comments around SQL backups.

Kind regards

Edward

pkh's picture

The complexity stems from the way jobs are created in BE 2012.  There is one selection list for all the "jobs", full, log, etc., in a job.  This is done to prevent you have having a different selection list for your full and incremental backups which can have potentially disasterous consequences.

In earlier versions of BE which allows you to have different selection lists.  You can have a full database backup which select the SQL instance and thus all the databases.  Adding/deleting databases will automatically update this selection list.  For the log backup, you have another selection list which only have the databases with full recovery mode.  If you add another full recovery mode database and did not update the log backup selection list, you will know about this sooner or later because the log not truncated error message will pop up.

kf2013's picture

I think edward wants to know how to schedule a job to backup the SQL transaction log only.

In BE 12.5, we can schedule to backup SQL transaction log only, but in BE2012, you have to schedule a full backup of SQL Database.

The following option is not avaiable when you schedule the backup job. It is only available in One-Time backup.

I am also finding a way to backup transaction log only with schedule

be2012.png