Video Screencast Help

Backing up SQL Databases | Full / Simple Recovery Mode Issue

Created: 09 Nov 2012 | 3 comments


We are using BE 2012 SQL Agent to backing up our critical SQL DB.

Our backup strategy: Weekly full backup + daily differential backup.

Our problem is, that our business needs to use SQL full recovery mode to keep ability restoring SQL DB with limited or no transaction loosing.

After 10 successful full backup, we got an exceptions as indicated in the following job completion message; "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."

We found the explanation, that BE 2012 should maintain the log backup. However in BE the SQL log backup is possible just with incremental backup, not with differential or Full backup.

What should be do? We don't wanna change Full+Diff backup strategy and we need Full SGL DB recovery model.

We figured out, we will create a simple SQL script and we will run after every Full backup as CMD with post command.

Here what we are doing:

USE ReportServer;

-- Truncate the log by changing the database recovery model to SIMPLE.
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ReportServer_log, 1);
-- Reset the database recovery model.
And the CMD file which run as post command:
osql -i shrink.sql -U sa -P ******** -S -d ReportServer
Unfortunately this SQL script not gave is successful result: we got always this annoying exception message.
Out question is: how we should backup SQL to dismiss this exception?
Thanks the answers, regards,

Comments 3 CommentsJump to latest comment

VJware's picture

Its still possible in BE 2012 with these methods added to the SQL backup definition...

Full backup + incremental (log type) + incremental (differential)...

this will achieve exactly what you wish to accomplish.

monicomp's picture

Hello VJware,

Thanks your effort, but I think this is wrong!
Incremental and differential backup methods cannot be combined in the same backup definition. (You can't add differential job if you have incremental job already).
As I wrote earlier, we need Weekly Full backup and Daily differential backup.
However business needs to use SQL Full recovery model.

Please explain how to backup Weekly Full + incremental (log backup) + daily Differential.
It is surprising non of Symantec support employee can answer or explain lot of simple BE 2012 question.
Maybe we misunderstanding something or they not familiar with BE 2012.

I hope you can help us.

VJware's picture

You are right that usually incr & diff backup methods cannot be combined in the same backup def...But I am 'not' proposing the same thing...My statement reads:-

Full backup + incremental (log type) + incremental (differential) essence, this is a full + log + diff