Jump to content


Photo

SQL recovery set to simple or full, what's better?

sql full simple recovery

Best Answer Ben Paxton, 26 April 2012 - 03:44 PM

If you'd prefer to run all of your databases in simple recovery model, thats fine too. Simply change the backup schedule so that it's doing FULL backups rather than log or SMART and you won't see those warnings anymore.

There isn't an ignore option and maybe there should be. The idea behind the warning however is to ensure you're being properly notified that there is a more efficient way of backing up the database(s). The only situation in which an ignore option would be valuable in my opinion is mixed environments where some databases are set to full and some are set to simple (or another recovery model). Go to the full post


  • Please log in to reply
11 replies to this topic

#1 Richard Cavallaro

Richard Cavallaro
  • Members
  • 1 posts

Posted 27 December 2011 - 04:39 PM

Everyone,
I have a slight debate going on in house on simple vs. full recovery mode for sql databases and was wondering if anyone had any input. The debate is our SQL team wants simple mode for the databases while the barracuda likes it to be set to full recovery mode. Rather than get into the debate of what's better. Can someone explain in layman's terms what the advantages and disadvantages of each are? The way I understand it and please correct me

Full
+better for recovery
-takes up more space with transaction logs
-slight sql performance decrease

Simple
+least amount of disk space
-limited to last full backup

#2 Ben Paxton

Ben Paxton

    Technical Delivery Manager

  • Product Managers
  • 59 posts

Posted 03 January 2012 - 09:34 AM

Barracuda suggests that you use the Full recovery model for SQL databases so that the BBS can leverage transaction logs for incremental backups. There are a few things to keep in mind when determining the best course of action for you however.

First, how big are your databases? If the databases that you're backing up are small and the backup server is able to drain it's queue in a reasonable amount of time while doing full backups nightly, then there is little reason for the BBS to do transaction log backups. In this case simple recovery mode should be sufficient.

Second, are you concerned with the amount of storage in the cloud? Full database backups take up more space in cloud storage than transaction log backups. Ultimately you may end up paying more for cloud storage if you're doing full backups all of the time rather than a combination of Full and Incremental (transaction log) backups.

#3 mickey macon

mickey macon
  • Members
  • 3 posts

Posted 03 April 2012 - 12:18 PM

If i choose to keep running Simple Recovery Mode, is there a way to tell the BBS not to warn me about this ? Maybe an "ignore warning messages about Sql Simple Recovery Model" button hidden somewhere ?

#4 Ryan Hinkamper

Ryan Hinkamper
  • Members
  • 19 posts

Posted 06 April 2012 - 08:32 AM

It would be very nice to have an ignore button.

#5 Greg Kujawa

Greg Kujawa
  • Members
  • 1 posts

Posted 10 April 2012 - 01:36 PM

Due to those nagging informational alerts I changed over to the Full recovery method for my SQL 2008 user databases. Looking in the cloud I see about 4 versions of the full DB backups for each of these. But each night I'm getting logged transaction log backup failures. They are in the event logs as 3041 errors.

When I changed the recovery model I didn't restart SQL or anything. I figured maybe manually pulling a tlog backup would free things up for BBS. I just did that in SQL 2008 EM. We shall see...

#6 Ben Paxton

Ben Paxton

    Technical Delivery Manager

  • Product Managers
  • 59 posts

Posted 26 April 2012 - 03:44 PM   Best Answer

If you'd prefer to run all of your databases in simple recovery model, thats fine too. Simply change the backup schedule so that it's doing FULL backups rather than log or SMART and you won't see those warnings anymore.

There isn't an ignore option and maybe there should be. The idea behind the warning however is to ensure you're being properly notified that there is a more efficient way of backing up the database(s). The only situation in which an ignore option would be valuable in my opinion is mixed environments where some databases are set to full and some are set to simple (or another recovery model).

#7 Fritz Coyro

Fritz Coyro
  • Members
  • 1 posts

Posted 09 April 2014 - 03:16 PM

I agree it would be nice for the ignore.  We have a mixed mode (one random test that needs simple) and the alert comes up every day.



#8 crickwilli

crickwilli
  • Members
  • 6 posts

Posted 22 July 2016 - 12:19 AM

An advanced professional SQL database recovery software, download Kernel for SQL Database Recovery Tool. The tool quickly and safely recovers lost data, tables, views, stored procedures, rules, defaults, user defined data types and triggers from corrupt MDF database. Visit here - database.sqlrepair.org



#9 hazelkirksey

hazelkirksey
  • Members
  • 5 posts

Posted 26 July 2016 - 04:32 AM

If you want to recover corrupt SQL MDF files then, you can use Sql Server recovery because this tool is a relevant solution to recover damaged SQL MDF & NDF file and doesn’t make any kind of changes to SQL database, while repairing them. It’s available with free demo facility visit: http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html
 



#10 JeffreyWatson

JeffreyWatson
  • Members
  • 1 posts

Posted 16 August 2016 - 04:35 AM

This recovery tool recovers SQL database files in batch and allows users work on them again. To know more information of this tool so visit on :-   http://www.tools4recovery.com/sql-database-recovery.html



#11 Blue Star - 600 Group

Blue Star - 600 Group
  • Members
  • 1 posts

Posted 16 January 2017 - 02:43 PM

@Technical Delivery Manager,

 

you stated:  "If you'd prefer to run all of your databases in simple recovery model, thats fine too. Simply change the backup schedule so that it's doing FULL backups rather than log or SMART and you won't see those warnings anymore."

 

does this mean that when we are running backups in simple recovery mode that when an hourly backup runs and we get this error that it is not backing up anything useful since simple recovery will only allow restores from the last full backup?

 

thank you,

 

Buddy Farr



#12 Shawn Lubahn

Shawn Lubahn
  • Barracuda Team Members
  • 126 posts
  • LocationAnn Arbor, MI

Posted 03 February 2017 - 08:48 AM

Hi Blue Star,

 

When your databases are using the simple recovery model, Barracuda is not able to perform an incremental backup job because their are no transaction logs to backup. Barracuda defaults to a full backup every single time, hence the warning about not being able to do incremental backups. So I wouldn't say we're not backing up anything useful as we're protecting your entire database(s). Here is some additional documentation that may be useful:

 

https://campus.barra...LBestPractices/

 

https://campus.barra...BS/SQLKeyTerms/

 

Thanks,

 

Shawn