Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Part 2: Why you should be using the Full Recovery Model


Part 2: Why you should be using the Full Recovery Model

Author
Message
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 1558
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
Nicole Garris
Nicole Garris
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 143
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.
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 1558
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
Nicole Garris
Nicole Garris
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 143
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.
Sideout1972
Sideout1972
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 116
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?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9829
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Sideout1972
Sideout1972
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 116
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.

Festeron
Festeron
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 230
If you would like the default recovery model to be Full, then change the recovery model on the model database.



Festeron
Festeron
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 230
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.



Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9829
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search