October 1, 2010 at 9:16 am
SwayneBell (10/1/2010)
Hi Gail;Yes, the logs are mirrored with RAID 1.
See my 'RAID is not a backup' comments. In that case, your recovery plan is not worth the virtual paper it is printed on. If the log fails (corrupted) (and there are enough ways even with RAID), you've lost EVERYTHING back to the last full backup.
If you'd meant database mirroring, then there's a chance it would have worked.
Also, I'll get someone to show me the exact job, but our sysadmin says it actually deletes the file once the backup is done.
If it did, then the recovery plan is worth even less. If you rebuild (or truncate) the log after a full backup, the DB is in the equivalent of simple recovery until a full/diff back is made and any attempt at a log backup will be met with an error 'Cannot take log backup because there is no full backup'
If he really means deleting the file, then either SQL would have to be shut down or the database detached or taken offline in order to delete the file. SQL will NOT allow the file to be deleted while the DB is in use.
I suspect he's talking garbage here. I seriously doubt the tool will be deleting the actual log file.
If you're curious, see this for what deleting the log file can get you.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
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
October 1, 2010 at 10:08 am
See my 'RAID is not a backup' comments. In that case, your recovery plan is not worth the virtual paper it is printed on. If the log fails (corrupted) (and there are enough ways even with RAID), you've lost EVERYTHING back to the last full backup.
Don't beat around the bush Gail. Please tell me what you really think 🙂
When I read your comment I began researching RAID1. There's lots of information on what it is, but I'm still looking for material on why it doesn't work at as a backup.
If you rebuild (or truncate) the log after a full backup, the DB is in the equivalent of simple recovery until a full/diff back is made and any attempt at a log backup will be met with an error 'Cannot take log backup because there is no full backup'
He's assuming that SQL Server will just "grow a new logfile" that can be applied to the full backup.
If he really means deleting the file, then either SQL would have to be shut down or the database detached or taken offline in order to delete the file. SQL will NOT allow the file to be deleted while the DB is in use.
I didn't think you could do that either - i.e. I agree with you. At best, it sounds like a 'kludgy' way of doing things. I've got to get a look at the actual jobs on DPM.
I suspect he's talking garbage here. I seriously doubt the tool will be deleting the actual log file.
I'll point it out to him. I'm making lots of friends with our backup folks (not). At least the CIO likes me!
Also, I'll read the article you included on losing the transaction logs....
Thx Gail
October 1, 2010 at 10:31 am
SwayneBell (10/1/2010)
See my 'RAID is not a backup' comments. In that case, your recovery plan is not worth the virtual paper it is printed on. If the log fails (corrupted) (and there are enough ways even with RAID), you've lost EVERYTHING back to the last full backup.
Don't beat around the bush Gail. Please tell me what you really think 🙂
If you insist. I've been fairly restrained so far. :hehe:
When I read your comment I began researching RAID1. There's lots of information on what it is, but I'm still looking for material on why it doesn't work at as a backup.
RAID 1 is a form of high availability. It's a duplicate copy of the drive so that, if one fails, the other drive will keep running until the failed drive can be replaced. All it protects against is single drive failure.
Backup implies a copy of (whatever) elsewhere for recovery purposes. Emphasis - elsewhere. With a proper backup strategy I should be able to recover my database to near point of failure even if the entire server room is destroyed.
I have personally seen a database on a RAID 10 array completely destroyed (beyond recovery) because of a SAN controller glitch. The controller wrote garbage across all of the drives totally ruining what was on them (primary data file, log file, backup). Backups would have protected from that. RAID? Not so much.
I'm far from the only one:
http://www.sqlservercentral.com/Forums/Topic991798-266-1.aspx
http://www.brentozar.com/archive/2009/01/why-back-up-ask-journalspace/
He's assuming that SQL Server will just "grow a new logfile" that can be applied to the full backup.
It might 'grow' another log file, no guarantees though. It won't apply to the full backup though. The deletion would break the log chain.
Though, if this was happening, the log reuse would not show log backup, not until another full/diff backup ran.
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
October 1, 2010 at 10:58 am
If you insist. I've been fairly restrained so far. :hehe:
Oh, I know you have... I've read lots of your other posts. (Note to self: is that a light-saber her avatar is holding? She likely carries one)
Backup implies a copy of (whatever) elsewhere for recovery purposes. Emphasis - elsewhere. With a proper backup strategy I should be able to recover my database to near point of failure even if the entire server room is destroyed.
I see your point. We will change this. I read both the articles you included in your post.
The deletion would break the log chain.
Though, if this was happening, the log reuse would not show log backup, not until another full/diff backup ran.
Well, I'm not going to theorize with them for too long. I'm just going to cut to the chase and get them to restore to a point-in-time, as you advised earlier so we can determine if that's even doable here.
October 1, 2010 at 11:33 am
SwayneBell (10/1/2010)
Well, I'm not going to theorize with them for too long. I'm just going to cut to the chase and get them to restore to a point-in-time, as you advised earlier so we can determine if that's even doable here.
This is the best method to make your point, but a comment you made earlier made me think for a moment. I've dealt with network admins who insist, incorrectly, that they're doing things right, mostly because they're taking on too much.
However, the political thing to do here would be give them a 2 day warning that you're going to convince the CIO to do a point in time restore. They've got that long to get their acts together, or work with you on being able to perform it, since they can't right now. This will keep your relationship salvageable down the road with them. 🙂 If the system does go down you don't want them to hand you the keys to the server room, tell you the tapes are in the cabinets, and then they head home for dinner. XD
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 1, 2010 at 11:55 am
Your point is well taken Craig.
Everyone wants the same thing here - i.e. a sensible and reliable backup/recovery plan.
October 7, 2010 at 7:17 am
SELECT name, recovery_model_desc from sys.databases
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply