October 20, 2009 at 6:22 am
Other than Point-in-time recovery using FULL backups (along with Transaction Log backups), is there any other benefits to FULL vs the SIMPLE recovery model.
Thanks
Dan
October 20, 2009 at 7:02 am
Generally, the Full Recovery is used because a Full backup takes more time than a Log backup. Systems that can't take the time to run a Full backup several times a day will run log backups through the day and a Full backup when system usage allows (through the night, for example). Recovery takes more steps as there is a Full backup to restore and then any number of log backups, but at least there is a backup that doesn't take them back a full day or more.
BrainDonor.
October 20, 2009 at 7:04 am
but if Point-time-is not important for a database (as per business rules, in a crisis), then are there any other benefits to do a FULL recovery model?
October 20, 2009 at 7:18 am
The main place to use Simple recovery is in systems where either all data changes are through scheduled ETL processes, or systems where all data changes can be re-run for whatever reason.
For example, I have databases where the only data changes are from overnight ETL processes. No changes throughout the day. Take a full backup once after the processes are all done, and there are no transactions to back up. Simple recovery for that one, definitely.
I used to have a database that was simply a processing space for address lists. The whole process was highly effiicent and completely deterministic. In that particular case, it would actually be more efficient to reload the address lists for the day than to run a point-in-time recovery. Thus, nightly full backups and Simple recovery.
It's also theoretically possible that you could have a database that's so well protected by physical redundancy that you would be better off using a mirrored copy of the database instead of recovering from a backup anyway. The whole point of backups is redundancy, which could also be achieved through other means, like active/passive clusters. If you have that, and you want to use the logs for auditing purposes, then you might want Full recovery without setting up point-in-time recovery. Would chew up a ton of disk space (since the log would never truncate), but that might be fine. I can't think of a situation where I'd want to do that, since more standard solutions would accomplish the same thing more easily, but it's theoretically possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 7:24 am
AFAIK, no other benefits. both use the log, write transactions in two steps. The only benefit is to handle recovery with something other than a full backup.
October 20, 2009 at 8:01 am
Thanks for your inputs. That makes it clear.
Also the examples were helpful, GSquared.
October 20, 2009 at 9:10 am
I can think of one other benefit of being in full recovery and taking log backups regularly: If someone accidentally does something stupid in production like truncate the wrong table, issue an update and forgot the where clause, etc... you can use a log explorer tool to figure out who did it even if the data is recoverable in some other manner.
October 20, 2009 at 9:15 am
George, good point. But there is not in-built log explorer in SQL Server, is there?
October 20, 2009 at 9:30 am
That's the kind of thing I meant in my examples when I mentioned using the logs as an audit method. If you check my articles on audit trails and logging for SQL, there's more data there on how to do that, in the section on passive auditing.
Yes, you'd need a parsing tool. There are some good ones out there.
More likely, you'd want to have a trace running and use that. Combine a trace with full recovery and point-in-time restore, and you'll have what you need, without use of a third-party log parser. Cheaper, more efficient, and just as effective. Hard to argue with that. But it does depend on having a trace running.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 9:40 am
GSquared
I got the link http://www.sqlservercentral.com/articles/Auditing/63247. Thanks.
When you say trace, you mean profiler?
Dan
October 20, 2009 at 9:48 am
I prefer server-side traces over Profiler, but it's the same concept.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2009 at 10:51 am
Definitely agree with GSquared on having the server side trace running. I almost always have one running on my key production systems and there are tons of articles available online about how to set it up. I also use the free Cleartrace.exe tool listed in a recent SQL Magazine article on free tools to parse the trace files and aggregate the data.
October 20, 2009 at 12:04 pm
On a related note, is there any benefit to having a database in either the Full or Bulk-Logged recovery model if log backups are not occurring?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 20, 2009 at 12:07 pm
Slight benefit that you can use the log for an audit trail. Major drawback that it's going to consume more and more drive space as time goes on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply