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

BE2012: Backing up SQL Databases | Full / Simple Recovery Mode Issue

Created: 28 May 2012 • Updated: 29 May 2012 | 10 comments
jc-pro's picture
This issue has been solved. See solution.

Hey all,

When performing a SQL Full backup using the 'Full Recovery Model' the backup succeeds but with 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."

Now when changing the Recovery Mode of the database from Full to Simple as required above using SQL Management Studio and then when performing subsequent Incremental backups the following job completion message appears: "V-79-57344-33960 - A log backup was attempted on database database_name 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." 

Essentially what these two errors are saying is, "Use Simple Recovery, no wait, use Full Backup, no wait, use Simple Recovery, no wait, use Full Backup", and it can't decide which.

The error's contradict themselves, does anybody know how to tackle this issue?

Thanks

-j-

Comments 10 CommentsJump to latest comment

Carlos_Quiroga's picture

Dear Sir:

When using Full recovery mode, you must insert a Log backup once in a while to prevent the logs from growing too much. This is what the program is telling you.

If you do not want to insert log backups, then you should use Simple Mode.

When using Simple Mode, SQL Server handles what are called "checkpints" automatically and therefore there is no way to perfrom log backups.

So the idea is: if you run in Full Mode, do a log backup every now and then, or switch to Simple Mode, in which you cannot do log backups.

There is a very nice explanation on recovery modes in the book SQL Server 2005 Bible by Wiley if you can get your hands on a copy.

Regards,

RahulG's picture

Refer the best practice doument 

http://www.symantec.com/business/support/index?page=content&id=HOWTO74429

points from the above document 

    • Use only the SQL Agent to perform SQL full, differential, and log backups. If you use a third-party application, Backup Exec fails the differential and log backup jobs until you make a new full backup with the SQL Agent.

  • Run transaction log backups if the database is configured for the full recovery model to prevent unlimited log file growth. Backup Exec generates a success with exception job warning after 10 non-log backups in a row.

  • With the simple recovery model, copies of the transactions are not stored in the log file, which prevents transaction log backups from being run.

VJware's picture

Regarding the second error after changing to simple recovery mode...ensure there is not log backup method defined in the SQL backup job...and run a full backup first

Colin Weaver's picture

For information if you wamt to do Transaction Log (Incremental) backups then you should see the reminder message in the Full backup job only every 12 full backup jobs - at least that is how it worked in BE 2010.

So your choices are

Full Recovery Model, performing Full and Log backups and just accept a warning in the full backup every now and again

Simple Recovery Model and not doing log backups

You should really be talking to your SQL dba for what recovery model is best for your requirements and then implmenet the appropriate backup configuration based on the SQL requirements - don't base your SQL settings on the backup product operation

jc-pro's picture

If I choose to do Simple Recovery mode backups, is it best practice to then remove the Daily Incremental (log) backups from occuring?  Is there any way to do incrementals for Simple Recover mode backups?

VJware's picture

Can't perform incrementals for simple recovery SQL, however you might consider mix of full & differentials..

jc-pro's picture

That sounds like a really easy workaround, are you saying that by changing the databases to use Full recovery mode and also changing the daily incremental backup from 'Log' to 'Differential' this would remove the exception warning from appearing in the job log?

VJware's picture

are you saying that by changing the databases to use Full recovery mode

Not by changing the recovery model...this remains in simple recovery & you can consider a mix of full backups & differential backups...

If you switch over to the full recovery model, then log backups are required...

Again, best to check with SQL admin as to what are your requirements as the BE backup strategy depends upon the recovery models...

Colin Weaver's picture

You cannot do incremental with Simple - please review the Microsoft SQL documentation on differences between Simple and Full Recovery for why this limitation exists.

Not sure about the differential comment as I thought that was still a log backup just a log backup with no truncate which would also not work with Simple mode

EDIT: just did some googleing - differential backups are allowed with Simple Recovery Model

Example, 3rd party website that discusses it from an MS point of view

http://www.mssqltips.com/sqlservertutorial/4/sql-s...

SOLUTION
jc-pro's picture

That seemed to do the trick Colin, setting the incremental backup to use differential instead of Log results in a successful backup.