How to backup SQL logs and truncate them in BE 2012

This article is quoted from http://www.symantec.com/connect/articles/how-backup-sql-logs-and-truncate-them-be-2012

If your SQL database is set to Full Recovery Mode, it maintains transaction logs.  If these logs are not truncated from time to time, they will grow and eventually fill up your disk.  BE will warn you that you need to truncate your log.

V-79-40960-37914 – Database database_name is configured to maintain transaction logs.  Transaction log backups are not being performed.  This will result in the log growing to fill all available disk space.  Regular log backups should be scheduled or the database should be changed to the simple recovery mode.

Before you change the recovery mode to Simple, you should check with the database owner because some applications require a Full Recovery Mode database.

To truncate SQL transaction logs on a regular basis, you need to set up a SQL log backup job which will backup the log and truncate it.  This is not so apparent in BE 2012 because BE 2012 requires you to do a full SQL database backup before you can backup SQL logs.

1) Create your SQL backup job.

Make sure you only select the Microsoft SQL Instances.  Do not include files in the backup because you would need to turn off AOF.

2) Edit your SQL backup job

3) Turn off AOF

You would need to turn off AOF by unchecking Use snapshot technologs.  Otherwise, you may encounter problems when you try to re-direct your SQL database restore later.

3) Check that the incremental job is backing up the transaction log

If you do not want to do differential backups of your SQL databases, then this is all for the SQL part.  All you need to specify the schedule for the jobs, the media to use, etc.

You should schedule the log backup to be done AFTER the full database backup.  If you run the log backup before the full database backup and the database backup fails, then you might end up with nothing to recover your database.

4) Set up your differential SQL database backup

This step is optional.  Add another incremental job.

I know it is strange to add an incremental job to do a differential SQL backup, but you can only do differential backup on SQL databases, not incremental backup.

5) Change the method of the additional incremental job

If you have differential database backups, you can either do your log backups after the full backup or after the full and differential backups.

Notes

a) You can also truncate the SQL transaction logs on a one-off basis.  To do this, set up a one-time backup job, select only the SQL databases and then turn off AOF.  You then choose log for the backup option as in the screenshot below

b) Note that truncating the transaction logs does not recover the space occupied by the logs.  You would need to compact the logs to recover the space.  See these documents

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

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

c) If your database is set to Simple Recovery Mode and you attempt to do log backups on them, you will get this warning message

V-79-57344-33960 – A log backup was attempted on database discover that is not configured to support log backups. To change the configuration, use the SQL administration tools to set the recovery mode to Full.  A new full backup should be performed if this setting is changed before a log backup is run.

Note that some databases, like the Master database, cannot be set to Full Recovery Mode.

If you have a mixture of databases, i.e. some with Full Recovery Mode and some with Simple Recovery Mode, then you need to set up two backup jobs.

Job1 – with log backups, for databases with Full Recovery Mode

Job2 – without log backups, for databases with Simple Recovery Mode.  In Step 2 above, you would delete the incremental job.