| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2007-03-15 : 06:58:02
|
| If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?Drew |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-15 : 10:24:17
|
| AFAIK SQL Server will block them.Kristen |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-03-15 : 10:28:54
|
quote:
AFAIK
I had to look that one up, I thought you were swearing at me!I don't think SQL Server blocks them as the tran log job will start and end whilst the complete backup job is running on the same database.Drew |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-15 : 10:44:46
|
"I thought you were swearing at me"No mate, I'm not eloquent enough to swear using 5 letter words  I reckon my TLog backups get blocked by FULL Backups on SQL 2000 ... but that's no help on SQL 2005 of course.I also reckon that, again on SQL 2000, a FULL Backup appends the TLog stuff once it has finished - so that a FULL Backup is consistent as-off the END TIME of the backup, not the beging. So any other ad-hoc TLOG backup, presumably, can NOT delete any log entries.So I guess that if a TLog backup can run during a Full backup then at the least it has to leave the TLog entries in place for the Full Backup to use. Maybe if flags them as "done" and the Full backup can then free them up.But this is entirely speculation on my part .... I didn't find anything in BoL2k5 that clarifies itKristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-03-15 : 11:13:36
|
| You're right. The TLog is blocked during the Full backup ("Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo")The TLog back up job I was refering backs up TLogs from several databases, so it appeared to look like it was backing up all of them, but it must have been backing up all the others bar the database that the Full backup is working on, and so didn't error.Drew |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-15 : 11:24:37
|
I find that a bit scary .... get the timing wrong and you'll never manage to back up your TLogs !!Also, if you think you are backing them up every few minutes but a TLog backup gets blocked by a Full backup, which in turn is struggling to finish because of heavy database activity ... the "we won't lose more than 10 minutes work because of our TLog backups" statement-of-promise to the users is going to look a bit "thin"  Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-03-15 : 11:55:11
|
| "we won't lose more than 10 minutes work because of our TLog backups"How many time's have I used that exact phrase...So what are we saying:If I create FULL1.bak at 12:00 (takes 5 minutes to complete)TLog1.trn at 12:12TLog2.trn at 12:22Full2.bak at 12:30 (takes 5 minutes to complete)TLog3.trn at 12:32 (but get's blocked)TLog4.trn at 12:42 (successful)Stacy from finance runs in.. "Drew Drew, restore the database to what is was at 12:40, and I'll have your babies!!"I stare at her... scratch my head, and then discover that it can't be done? Or did the database backup everything since Full2.bak in TLog4.trnAnything before TLog.trn wouldn't be an issue because of Full2.bak.Drew |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-15 : 16:29:57
|
| If Stacy from finance runs in and wants to have your babies then forget about restoring the database!You can use the 12:42 TLog4.trn to restore to 12:40.However, if you have a hardware crash between 12:32 and 12:42 you can only restore to 12:32 ... and if you Full2.bak took an hour or two, instead of just 5 minutes, then the real-world situation needs thinkink about in a "disaster-recovery strategy meeting" (I reckon we can charge more for one of them than a "IT meeting" - what do you think?!)Kristen |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-15 : 17:14:15
|
| I don't know for sure, but I'd say if you had a crash during a long backup, you should be able to recover the transaction log with NO_TRUNCATE and then restore from your last good full, plus log backups from before the backup that crashed plus the NO_TRUNCATE log backup. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-03-16 : 04:42:23
|
| I've never really given it much thought (and never stunmbled into problems) because the complete backups are all done over night when the database is not in use. Saying that, the vast majority of tlog jobs do stop in the evening before the full back ups kick in.For any of my potential future employers that may stumble across this thread, I would like to point out that my original posting at the top of this page specified "ad-hoc" db backups!Drew |
|
|
|
|
Should TLog backup be suspended during full backup