SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by John Sansom / Part 2: Why you should be using the Full Recovery Model / Latest Posts

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

Nicole Garris — Tue, 26 Apr 2011 10:04:14 GMT

Interesting. Thanks for sharing.

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

Maramor — Tue, 26 Apr 2011 08:14:31 GMT

Your sophisticated method for tape sounds like something similar to what we have. (NetBackup)I totally would like to write to disk and have them back that up to what ever medium they wish. This method would cost a good bit in storage but I think it is well worth the cost for stability and I can manage the backups for SQL. We have a happy mix of large and small databases majority are ranging from 1 - 10 GB and a handful of them upper 30 GB and 1-2 in the 300 GB range.As far as the relationship with the Network Admins it is not that it is bad, I guess its just neither knows enough of the other world :). I have been digging into the SAN information (NetApp) to understand it better and try and speak intelligently about the products but without being able to touch and play it is very difficult. After a little further research, the SAN uses a client end to interface with SQL much like the tape method you mentioned. The Client pauses, takes snapshot of SQL and then I think it triggers a drive level copy (not 100% sure how that fully works, yet). This side works and I use this on occasion to make copies for dev with the larger databases. Now keep in mind I use the client side to control how often they occur and type of backup. Currently, we kind of use this as a nice to have backup where tape is the major line of defense. As I mentioned, if the SAN backup method runs out of space then I have to clear some or all backups or I lose the drive. If I was in full recovery mode and taking log backups say every 15min and have to clear backups I lose the integrity of full recovery mode. with the added question what do I do for the tape backup if log backups occurring every 15min from another system (SAN). I would imagine a possible scheduling conflict could occur let alone a pain to restore. I haven't seen anyone mention how they balance the different methods and I can not make a recommendation to our team.The SAN we are using could be used as a source for tape backups, which I asked about. The reply is "We just don't have resources to dedicate to figuring out how as it requires lots of custom scripting". Anyway, perhaps someday we will find a better solution :).

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

Nicole Garris — Mon, 25 Apr 2011 14:52:48 GMT

Sophisticated method: Tape backup systems (the one we use, for example) will work with a purchased agent that can be installed on the SQL Server's server. When the tape backup system wants to back up, it contacts the agent, which contacts VSS (a SQL Server service on the SQL Server) to back up the databases to tape.Dumbed down method (the one we use): Pick a time which is likely to be before the tape backup window of opportunity, and create a SQL Server maintenance plan/job which backs up the databases at that time to a specified folder (for example, SQLBackup). Tell the tape backup system to back up folder SQLBackup. This method wastes resources, including the space needed for the backups (stored on disk as well as on tape), I/O (writes to disks and then copied to tape), etc. We use it because our databases tend to be small, that is, 100 MB to 1 GB, and we have long windows for backup.I have so many questions for your network admin, you really really need a better relationship with that person. (Example of questions--what are the SAN backups doing? And are they interfacing correctly with SQL Server? If so your backups may be right there ...) Perhaps you can convince the network admin that they could solve some of their existing problems if they worked more closely with you... Good luck!

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

Maramor — Mon, 25 Apr 2011 08:08:56 GMT

Quick question hopefully :).Network team manages backups to tape and said tape system/application can not be setup to backup at specific times. It has some kind of queue system with windows of opportunity. Also, we have a SAN backup process that does 4 backups during business hours which I control times and frequency to some degree. Network team configures backend filer/SAN system wich is a black hole to me at the moment.Now all my databases are set to simple. My problem is, I do think we could benefit from Full Recovery Model in many of our databases. [b]So how would one overcome the challenge of balancing tape and SAN methods?[/b] And how does one deal with difficult Network Admins :) and managers who don't understand a thing you are talking about :).I absolutely hate tape backups :) and it's a love/hate relationship with SAN. Occasionally, I have issues with SAN method filling up and needing to remove snapshots do to a large transaction. I would love to write to disk and have them backup the disk drive :).

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

magarity — Wed, 23 Sep 2009 18:25:13 GMT

[quote][b]Jeffrey Williams-493691 (9/21/2009)[/b][hr]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.[/quote]This should be corrected to say: BackupExec and other utilities won't backup SQL Server database files [i]unless their SQL Server "agent" software is purchased seperately and installed.[b][/b][/i] Please see: http://www.backupexecfaq.com/articles/concepts/backing-up-microsoft-sql-server.html Other enterprise backup programs have similar - ask your vendor's rep.

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

John.Sansom — Wed, 23 Sep 2009 10:16:19 GMT

[quote][b]Festeron (9/21/2009)[/b][hr][quote]... other types of database that would not necessarily be suited to operation under the Full Recovery Model include ... databases that experience discrete infrequent modifications.[/quote]I cannot agree with you here.If you are on Simple, and your log was truncated this morning, and today you have had "discrete infrequent modifications", aren't you going to want to recover them?Frequency of modification has nothing to do with the choice of recovery model. You should be more concerned with data loss, of any size.In other words, a database with infrequent modifications could be an excellent candidate for the Full Recovery Model.[/quote]Hi Festeron,Thank you for your comments.There is no one solution fits all, perhaps I should have made my original point clearer, although I'm pretty sure I did NOT say "All databases with discrete infrequent modification should use the SIMPLE Recovery Model" :-DTo re-iterate my prior statement, with emphasis:[i]In addition to those you highlighted, other types of database that would [b]not necessarily[/b] be suited to operation under the Full Recovery Model include Read Only databases and databases that experience discrete infrequent modifications.[/i]So the point I was trying to be make is that using the FULL recovery model for static (read only) or relatively static databases might be overkill in some scenarios. For example, if a database is only updated say by a batch process once a month, the SIMPLE recovery model may be a more appropriate choice. Provided a FULL database backup is taken after the batch process, the database can be restored to it's current state at any point within the month.Thanks again for your comments!

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

John.Sansom — Wed, 23 Sep 2009 10:01:19 GMT

[quote][b]bas de zwart (9/23/2009)[/b][hr]I almost can't believe that *any* recovery model is presented like this as a 'best practice'; and certainly not as WHY you SHOULD be using this or that recovery model.It depends totally on the following factors:- What kind of organisation is it?- How many transactions are there per hour/minute/second- Downtime? How long does is take to go back to that point in time? - If you make a daily full and quarterly DIFFS you can lose 15 minutes of transactions at the most. Can these transactions be added manually again? What kind of transactions are they?So many questions that this depends on.What is worse? Taking 2 full hours to restore to a certain point in time (thereby losing 2 while gaining perhaps half an hour), or losing half an hour of data and have the organisation re-enter said data? Most often we find that organisations have shorter downtime (eventhough there's a loss of data) with the simple recovery model because restoring it is easy and often transactions can be resubmitted by the users themselves.There is NO set of rules or laws that state that you should use either Simple or Full recovery model, thinking in that manner is shortsighted at [b][i]best[/i][/b]. A smart DBA checks each database and defines a recovery model for each database depending on it's use. In practice, most databases with the Simple recovery model can be brought online far quicker than those with Full recovery models. Please do no take this shortsighted article on face value and think a little bit beyond shortsightedness. I've rated the article as 'awful', simply because it says that people SHOULD use the Full recovery model, it's a mindboggingly dumb statement. Sorry for my rude reaction, but my point should be made with a little bit of force behind it.[/quote]Thank you for your comments.You are absolutely right. Each and every database environment is unique and the specific requirements for a robust DR strategy require that evaluation be performed on a case by case basis. To emphasise this need, one of the closing points of the article reads:[i]“Database backups and SQL Server disaster recovery planning are detailed topics and require much more consideration, research and planning than are presented in this article alone.”[/i]I would also like to highlight that designing a DR strategy is not the topic being presented by the article. Taken from the article:[i]“To summarise the key message here, in order to be able to [b]restore [/b]your database to a particular [b]point in time[/b] you "must" be using the Full Recovery Model.”[/i]Once more, thank you for your feedback. It’s always good to see database professionals who are passionate about their work.

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

bas de zwart — Wed, 23 Sep 2009 08:55:49 GMT

I almost can't believe that *any* recovery model is presented like this as a 'best practice'; and certainly not as WHY you SHOULD be using this or that recovery model.It depends totally on the following factors:- What kind of organisation is it?- How many transactions are there per hour/minute/second- Downtime? How long does is take to go back to that point in time? - If you make a daily full and quarterly DIFFS you can lose 15 minutes of transactions at the most. Can these transactions be added manually again? What kind of transactions are they?So many questions that this depends on.What is worse? Taking 2 full hours to restore to a certain point in time (thereby losing 2 while gaining perhaps half an hour), or losing half an hour of data and have the organisation re-enter said data? Most often we find that organisations have shorter downtime (eventhough there's a loss of data) with the simple recovery model because restoring it is easy and often transactions can be resubmitted by the users themselves.There is NO set of rules or laws that state that you should use either Simple or Full recovery model, thinking in that manner is shortsighted at [b][i]best[/i][/b]. A smart DBA checks each database and defines a recovery model for each database depending on it's use. In practice, most databases with the Simple recovery model can be brought online far quicker than those with Full recovery models. Please do no take this shortsighted article on face value and think a little bit beyond shortsightedness. I've rated the article as 'awful', simply because it says that people SHOULD use the Full recovery model, it's a mindboggingly dumb statement. Sorry for my rude reaction, but my point should be made with a little bit of force behind it.

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

timothyawiseman — Mon, 21 Sep 2009 18:51:12 GMT

In general, a database should be in full backup mode for precisely the reasons described here. There are advantages to using the Simple backup model. The prime one being less concern about the transaction log growing or worry about them filling up. They can still grow of course (assuming autogrowth is on) in simple, but this will be a less frequent event under normal usage patterns using simple than using full, and there is no need to trake the time to truncate those logs. Also, certain types of transactions are generally faster under a simple model.

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

Festeron — Mon, 21 Sep 2009 15:59:30 GMT

[quote]... other types of database that would not necessarily be suited to operation under the Full Recovery Model include ... databases that experience discrete infrequent modifications.[/quote]I cannot agree with you here.If you are on Simple, and your log was truncated this morning, and today you have had "discrete infrequent modifications", aren't you going to want to recover them?Frequency of modification has nothing to do with the choice of recovery model. You should be more concerned with data loss, of any size.In other words, a database with infrequent modifications could be an excellent candidate for the Full Recovery Model.

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

Jeffrey Williams 3188 — Mon, 21 Sep 2009 15:57:06 GMT

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.

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

Festeron — Mon, 21 Sep 2009 15:51:46 GMT

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.

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

Festeron — Mon, 21 Sep 2009 15:43:28 GMT

If you would like the default recovery model to be Full, then change the recovery model on the [i]model[/i] database.

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

Sideout1972 — Mon, 21 Sep 2009 15:19:44 GMT

Jeffrey - thanks for the reply. Won't BackupExec or the other major backup products out there back up "open files"?[quote][b]Jeffrey Williams-493691 (9/21/2009)[/b][hr]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.[/quote]

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

Jeffrey Williams 3188 — Mon, 21 Sep 2009 10:40:44 GMT

[quote][b]Jason-181907 (9/21/2009)[/b][hr]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?[/quote]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.

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

Sideout1972 — Mon, 21 Sep 2009 10:25:52 GMT

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?

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

Nicole Garris — Mon, 21 Sep 2009 09:05:26 GMT

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.

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

John.Sansom — Mon, 21 Sep 2009 08:57:08 GMT

[quote][b]Nicole Garris (9/21/2009)[/b][hr]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.[/quote]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,

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

Nicole Garris — Mon, 21 Sep 2009 08:35:23 GMT

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.

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

John.Sansom — Mon, 21 Sep 2009 00:26:02 GMT

Comments posted to this topic are about the item [B]Part 2: Why you should be using the Full Recovery Model [/B]