Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-04-20 : 00:55:39
|
| I restored a database this morning, and I was a bit surprised that I had to restore the transaction log made just BEFORE the differential - is that normal?The sheduled task here makes a transaction backup of all DBs (hourly) and then either a DIFF or a FULL if its 4am - the FULL is done on Sundays.The restore sequence was:Restore last FULLRestore last DIFF (04:01)Restore TRANS (04:00) [I expected to start with 5:00!]Restore TRANS (05:00)... and so on up to latest TRANSRegards,Kristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-20 : 01:06:03
|
| It just depends on the LSNs of the backups. They have to be in sequence. The starting LSN of the 4am transaction log must have been smaller (or whatever the term) than the starting LSN of the differential backup. The 4am transaction log backup must have kicked off just milliseconds prior to the differential backup since they both occurred at 4am.Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-20 : 08:05:22
|
| ??? The differential isn't based on the LSN of the transaction log backup. It's based on the LSN of the full. Something else had to be wrong. You should be doingRESTORE FULLRESTORE DIFFRESTORE TRAN1 (now this one could be out of order, but not the other way around)RESTORE TRAN2MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-20 : 12:25:47
|
| That's what I was saying though, just not that clear.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-04-21 : 07:38:44
|
quote:
Originally posted by tduggan
It just depends on the LSNs of the backups. They have to be in sequence. The starting LSN of the 4am transaction log must have been smaller (or whatever the term) than the starting LSN of the differential backup. The 4am transaction log backup must have kicked off just milliseconds prior to the differential backup since they both occurred at 4am.Tara
Thanks Tara. The 4AM LOG and subsequent DIFF would have been run sequentially (i.e. both were run by the same "backup jobs" SProc), but I expect that the LOG had the best part of nothing very much in it, and thus the DIFF ran very very soon afterwards.Is there some way I can "know" the LSN - the RESTORE script is mechanically generated (saves typing in the script for all those pesky LOG restores!), and I'd like to get it right so that our regular muppets can use it. I guess I'd need to know the currently-needed-LSN when I make the DIFF (and when I make a LOG backup), and store that in my log, so that if I create a restore script, which includes that DIFF, it can work out which is the subsequently-required LOG file (even if it was create before the DIFF, as in this case).I also have the worry that we delete log files when their "parent" is deleted - I am currently attributing LOG files to the next-earlier FULL / DIFF backup, and that seems to be wrong in this case.Kristen |
|
|
|
|
|
|
|
|
Restore LOG before DIFF