Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Part 2: Why you should be using the Full Recovery Model Expand / Collapse
Author
Message
Posted Monday, September 21, 2009 12:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:15 AM
Points: 344, Visits: 1,530
Comments posted to this topic are about the item Part 2: Why you should be using the Full Recovery Model


John Sansom (@sqlBrit) | www.johnsansom.com
Post #790955
Posted Monday, September 21, 2009 8:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 3:06 PM
Points: 25, Visits: 131
I agree that the default recovery model should be Full. However there are cases where the simple model is more appropriate. For example, if its mostly large "batch" jobs that update the database, and losing a day of processing would only cause a small amount of harm to the organization, then the simple recovery model may be a lot easier to administer. Should you lose the database, its fairly easy to restore the latest backup and then re-run the batch jobs.
Post #791168
Posted Monday, September 21, 2009 8:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:15 AM
Points: 344, Visits: 1,530
Nicole Garris (9/21/2009)
I agree that the default recovery model should be Full. However there are cases where the simple model is more appropriate. For example, if its mostly large "batch" jobs that update the database, and losing a day of processing would only cause a small amount of harm to the organization, then the simple recovery model may be a lot easier to administer. Should you lose the database, its fairly easy to restore the latest backup and then re-run the batch jobs.


Hi Nicole,

Thank you for your comments.

I agree. There are indeed certain types of database with workloads that are not necessarily suited to operation within the Full Recovery Model.

In addition to those you highlighted, other types of database that would not necessarily be suited to operation under the Full Recovery Model include Read Only databases and databases that experience discrete infrequent modifications.

The key point that the article is endeavouring to communicate is that if you need to ensure that you are able to recover your database to a specific “point in time” then you must be using the Full Recovery Model.

Cheers,



John Sansom (@sqlBrit) | www.johnsansom.com
Post #791186
Posted Monday, September 21, 2009 9:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 3:06 PM
Points: 25, Visits: 131
Thanks. I'm glad you posted your article, its much needed. Over and over again I see OLTP-oriented databases which are installed as Simple which really should be Full.
Post #791200
Posted Monday, September 21, 2009 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 06, 2013 5:31 AM
Points: 29, Visits: 105
For those that use "simple" recovery model, when it comes to backups, does it matter if you are doing full backups or simply backing up the .mdf/.ldf files? Wouldn't both create the same effect?
Post #791293
Posted Monday, September 21, 2009 10:40 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
Jason-181907 (9/21/2009)
For those that use "simple" recovery model, when it comes to backups, does it matter if you are doing full backups or simply backing up the .mdf/.ldf files? Wouldn't both create the same effect?


You cannot backup the mdf/ldf file while SQL Server is running. The files are locked and will not be backed up using normal backup utilities.

There are SAN based utilities that utilize VDI to 'freeze' SQL Server and then snap the volume where the databases reside. However, this is not the same as backing up the mdf/ldf.

And since there is no reason to shut down SQL Server every night - I don't see any way to back those files up, so using native SQL Server backups is the appropriate way to make sure you have good backups.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #791303
Posted Monday, September 21, 2009 3:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 06, 2013 5:31 AM
Points: 29, Visits: 105
Jeffrey - thanks for the reply. Won't BackupExec or the other major backup products out there back up "open files"?

Jeffrey Williams-493691 (9/21/2009)


You cannot backup the mdf/ldf file while SQL Server is running. The files are locked and will not be backed up using normal backup utilities.

There are SAN based utilities that utilize VDI to 'freeze' SQL Server and then snap the volume where the databases reside. However, this is not the same as backing up the mdf/ldf.

And since there is no reason to shut down SQL Server every night - I don't see any way to back those files up, so using native SQL Server backups is the appropriate way to make sure you have good backups.
Post #791433
Posted Monday, September 21, 2009 3:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:17 AM
Points: 164, Visits: 195
If you would like the default recovery model to be Full, then change the recovery model on the model database.


Post #791443
Posted Monday, September 21, 2009 3:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:17 AM
Points: 164, Visits: 195
From the title of this article, I was expecting much more on why I should be using the Full Recovery Model. Instead, I got an introduction to data loss and an introduction to backups. The author didn't introduce "SQL Server Recover Models" [sic] until near the end of the article, just before "Final Thoughts". Even then, this section was mostly links to BOL or extracts from it.

I'm not suggesting that this is a bad or misleading article. However, I would definitely change the title to something more appropriate.




Post #791447
Posted Monday, September 21, 2009 3:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
No, BackupExec and other utilites like that won't backup SQL Server database files that are open. SQL Server locks the files and will not allow them to be backed up.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #791449
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse