September 26, 2011 at 11:37 am
I recently came across an organization with an unusual approach to Disaster Recovery: The databases all receive a full backup just before midnight, followed immediately by log backups to the same device for each database. Then during the next business day, hourly differential backups are taken and also added to the same device. A log backup is not taken until the regularly scheduled one at midnight.
The purpose of this approach seems to be as a simplification in the event of the necessity of restoring a backup during the day by a non-DBA IT person. There are a set of stored procedures in place which can be used to either restore the last full backup (from just before midnight), or to restore the database using the full backup plus the most recent differential backup. Because this strategy is fairly simple in concept and only requires the IT personnel to add the name of the database as a parameter to the set procedures, it is attractive to the organization.
There haven't been any problems with growth in the log files, no problems with space constraints, and the possibility of one hour of data loss is considered acceptable.
So is this a reasonable approach? All the recommendations I have seen involve multiple log backups, with less frequent differential backups.
September 26, 2011 at 11:45 am
You have up to an hour's data loss in any disaster and the transaction log is going to be many times the size it probably should be. If no one is considering tail-log backups, rather switch that DB to simple recovery
It's easy enough to write a procedure that automates restores with log backups involved, so the simplicity is not a good reason for that strategy
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2011 at 11:48 am
The log backup is essentially useless in this case. They'd be better off with just putting the database in Simple Recovery mode and not backing up the log. Same end result, less work, and even lower chance of runaway log growth.
Log backups exist only for point-in-time restore. Full Recovery exists for log backups. If you're not doing PIT restores, then there's no reason to back up the log and no reason to be in Full (or Bulk) Recovery. Well, minimal reason, since you might be using a log parser for audit purposes, but it's unlikely.
If they do want PIT recovery, it's actually easier than they think. In Management Studio, select a database, right-click and Tasks...Restore, pick the Restore from Database option, and select the point in time you want. It's really, really easy, and the computer will figure out which files to use for you. No real work on the part of a non-DBA doing the restore. Just needs to be able to use a mouse and follow the step-by-step directions on a form.
- 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
September 26, 2011 at 11:49 am
why don't they write a procedure to restore log backups ?
also you can also t-log backup can be done with diff backups so they wouldn;t be losing anything if they decided to use t-log backups but they would gain a whole lot more such as PIT restores.
September 26, 2011 at 11:59 am
Yes, that all makes sense. I think the idea was to have the databases in Full Recovery mode in case a DBA was available (via a service), and that DBA could do a log-tail backup as part of fixing a problem. (If lucky) But if a DBA was not available, procedures are in place for the non-DBA personnel on site.
I don't know why there is reluctance to use the PIT processes available in Mgmt Studio; fear of the unknown? lack of confidence in the personnel available?
September 26, 2011 at 12:01 pm
Ah, I just found part of the answer. This org wants to have documented procedures for all kinds of things. And it seemed easier to have prepared stored procedures, than to write a procedure on how to utilize the GUI SSMS.
September 27, 2011 at 6:27 am
Elliott Berkihiser (9/26/2011)
Ah, I just found part of the answer. This org wants to have documented procedures for all kinds of things. And it seemed easier to have prepared stored procedures, than to write a procedure on how to utilize the GUI SSMS.
So long as they understand that laziness on that can result in data loss, that's a valid business decision. Defining their recovery procedure based on "it would be hard to write a policy about it" is pretty darn lazy, after all.
- 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
September 28, 2011 at 3:49 pm
I don't want to be too harsh on these folks. Their business does not include the typical transactional database. Most of their input comes from 3rd party files, which can be re-loaded if there is a failure. Apparently they have decided that a one hour data loss can be tolerated: HR would re-load the last employee updates, Facilities would re-enter the latest machine depreciation, 3rd party FTP files would be re-run.
I see a lot of people/businesses who take no backups at all, or more commonly, take backups but have no restoration tools and certainly no testing. So these folks are at least trying, and they have tools that have been tested to bring back the data to at least within one hour of a screw-up. Their goal is to have a system that works without a DBA. :hehe:
That said, based on your feedback, I am going to try to convince them that frequent log backups should be added to the mix.
Thanks everyone for your ideas.
September 29, 2011 at 6:29 am
Elliott Berkihiser (9/28/2011)
I don't want to be too harsh on these folks. Their business does not include the typical transactional database. Most of their input comes from 3rd party files, which can be re-loaded if there is a failure. Apparently they have decided that a one hour data loss can be tolerated: HR would re-load the last employee updates, Facilities would re-enter the latest machine depreciation, 3rd party FTP files would be re-run.I see a lot of people/businesses who take no backups at all, or more commonly, take backups but have no restoration tools and certainly no testing. So these folks are at least trying, and they have tools that have been tested to bring back the data to at least within one hour of a screw-up. Their goal is to have a system that works without a DBA. :hehe:
That said, based on your feedback, I am going to try to convince them that frequent log backups should be added to the mix.
Thanks everyone for your ideas.
If it truly is okay to lose an hour's data, and in some places it really is, then switch to Simple Recovery on the affected databases, and drop the nightly log backup. That solves the problem nicely. And it can help prevent log explosions.
- 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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply