March 18, 2011 at 8:17 am
I just found out that someone has programmed backupexec to run full backups nightly.
It was a nice stroke of log luck while I was scanning the backup history to analyse db growth.
My question is about log chain and pit restore.
Backup exec takes a full backup (no log) at midnight.
I do checkdb, index maint, tlog backup (once daily) and then full backup from 3 to 5 am daily (db is on san and admin doesn't want to take tlog during the day because he feels it can't possibly break >>> pls comment on that if you have had bad experiences there!).
I'm wondering is my tlog backup is actually usable to restore to any point in time during the day.
March 18, 2011 at 8:35 am
"The SAN can't possibly break" -> ROTFL. The worst database disaster I've seen was due to a faulty SAN controller.
Test the backupexec full with the log. I have not heard good things about that, but I've never worked with it personally. Doing backups to 2 different places with 2 different technologies is not a particularly good idea.
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
March 18, 2011 at 8:42 am
My problem at this cie is they since I'm not "certified", what I say doesn't matter.
So I'm hoping to get more tractions with your comments and experiences :w00t:.
My point is to throw out backup exec because I do a lot more work with my script, smarter, more targeted, with e-mail notifications... the whole 9 yards.
I was asking about the log p.i.t. restore blackout zones because I can't even do a test restore (no room on the test server for a 2nd restore and I can't throw out the test db at the moment).
If you can't answer 100% sure from the top of your head, I'll just script something out to prove my point but I'd still like some confirmation from you Gail (or others) if you can.
March 18, 2011 at 8:59 am
A while back I did a restore from BackupExec, then applied transaction logs without a problem. I assume your BackupExec is a Normal "BACKUP DATABASE", and not a file backup or some sort of snapshot.
I only use BackupExec to tape for a database that doesn't have enough disk space to store the backup file. I prefer native (or 3rd party) backup tools
T-Logs once per day isn't very useful. what if you need to recover more recently than your t-log ? Recovery isn't just for drive failures. Look at all the posts here titled "Someone dropped a table ..." ... "I accidentally changed the wrong rows ..."
Every 15- 20 mins is what we do.
March 18, 2011 at 9:02 am
Tx, keep 'em coming!
March 18, 2011 at 9:03 am
"...I'm wondering is my tlog backup is actually usable to restore to any point in time during the day. ..."
Only up to the point of your most recent t-log backup. Currently you have potential of close to 24 hour data loss. For example, a disaster happens just before your t-log backup, you have to go back to your last full backup.
March 18, 2011 at 9:07 am
homebrew01 (3/18/2011)
"...I'm wondering is my tlog backup is actually usable to restore to any point in time during the day. ..."Only up to the point of your most recent t-log backup. Currently you have potential of close to 24 hour data loss. For example, a disaster happens just before your t-log backup.
I know that. He had me do 2 full daily and then hope we could take a tail tlog backup to do p.i.t. restore. But then we ran out of space and went back to daily...
I know this strategy is based more on faith than logic... keep poking at it!
I'm also pushing for a test restore now that we have enough time and room on the san to grow our test servers.
Any of you had a backup with checksum that still failed to restore?
March 18, 2011 at 9:13 am
Ninja's_RGR'us (3/18/2011)
He had me do 2 full daily and then hope we could take a tail tlog backup to do p.i.t. restore.
Tail log requires that the log file is intact. Lose or corrupt that and SOL.
Any of you had a backup with checksum that still failed to restore?
Sure. Checksum on the backup just let's SQL tell whether or not the backup file has been changed since it was backed up. By no means guarantees that it will restore.
If restore verify only succeeds on a backup taken with checksum, then the restore will succeed.
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
March 18, 2011 at 9:18 am
GilaMonster (3/18/2011)
Ninja's_RGR'us (3/18/2011)
He had me do 2 full daily and then hope we could take a tail tlog backup to do p.i.t. restore.Tail log requires that the log file is intact. Lose or corrupt that and SOL.
Any of you had a backup with checksum that still failed to restore?
Sure. Checksum on the backup just let's SQL tell whether or not the backup file has been changed since it was backed up. By no means guarantees that it will restore.
If restore verify only succeeds on a backup taken with checksum, then the restore will succeed.
Tx for "backing" me up :hehe:.
I can't find the link now, but I read from an mvp that even with checksum + verify the restore can still fail. Something about data in between the page not getting verified in the process. So that data could be fine, but the backupfile "structure" could make the restore fail too.
Anybody had troubles with that?
March 18, 2011 at 9:22 am
What is the reason for not taking frequent t-log backups ? I can't think of any bad reasons, let alone good reasons.
March 18, 2011 at 9:22 am
Ninja's_RGR'us (3/18/2011)
admin doesn't want to take tlog during the day because he feels it can't possibly break
No need to take ANY backups then 😀
I agree with the others... the extra full backup shouldn't get in the way of any PIT restores, but if BackupExec is doing transaction log backups as well as your scripts, I'd be a bit worried.
March 18, 2011 at 9:28 am
Ian Scarlett (3/18/2011)
Ninja's_RGR'us (3/18/2011)
admin doesn't want to take tlog during the day because he feels it can't possibly breakNo need to take ANY backups then :-D.
Awesome comeback!!! He actually feels the need there becauses users are allowed to do mass imports in the system and that sometime fails or screw up the data beyond repair!
Ian Scarlett (3/18/2011)
Ninja's_RGR'us (3/18/2011)
I agree with the others... the extra full backup shouldn't get in the way of any PIT restores, but if BackupExec is doing transaction log backups as well as your scripts, I'd be a bit worried.
It's only set to full, no checksum, no verify. So it only wastes valuable room in my humble opinion :w00t:.
March 18, 2011 at 9:30 am
homebrew01 (3/18/2011)
What is the reason for not taking frequent t-log backups ? I can't think of any.
Same here. I'm consulting as BI dev here and since I'm not DBA certified I have very little weight here. The admin is just a genius at networking and he has taken a 1 week SQL admin course and he thinks he knows enough.
So again, thank you for giving me extra traction here! Keep 'em coming.
March 18, 2011 at 9:32 am
homebrew01 (3/18/2011)
"...I'm wondering is my tlog backup is actually usable to restore to any point in time during the day. ..."Only up to the point of your most recent t-log backup. Currently you have potential of close to 24 hour data loss. For example, a disaster happens just before your t-log backup, you have to go back to your last full backup.
That actually kind of happenned once during an import. Still not enough to convince him (was on a sunday). Our setup is 23 / 6... so let's just say we were lucky there.
March 18, 2011 at 9:45 am
If you're consulting, and there's a disaster, maybe you'll get more billable hours to help fix it. 😀
Maybe someone in the business end needs to know they have 23 hour data loss potential (hardware, application error, human error) and are they "OK" with that ?
I suppose the Admin doesn't hit "SAVE" periodically when working on a spreadsheet for 5 hours ?? He just waits until the end ?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply