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.
