| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2007-10-17 : 12:23:39
|
| I have a question that I've always wondered about and never found an answer to. Years ago I set up log shipping in SQL2000 Standard Edition. I had an SP that I had written apply the logs at the remote server. I would pass the restore command a file_id to use since the log files were all backed up in a single file. The issue I had was with multiple error messages. I'm going to make up error #'s since I don't remember the exact numbers...just to get the point across. The actual numbers are irrelevant. Let's say the log restore failed because I tried to restore a log that's too early. I would get error messages like this:Error 1000 LSN too earlyError 2000 Restore terminating abnormallyLet's say it failed due to the log being too late. I'd get an error like this:Error 1100 LSN too lateError 2000 Restore terminating abnormallyIt may fail for anything and you always get:Error XXXX Some reason for failureError 2000 Restore terminating abnormallyThe problem is when you look at @@error it's always 2000. I needed to get the first error so I could have my code do something different depending on the error. I never figured out a way to get the first error since they are both returned at the same time and that made me had to go a round about way and jump through some hoops to get my script to work and email me when a failure to restore a log really occured. Anybody know how to get the first error? Is this better in 2005? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 13:05:56
|
| Well ADO gives you an Errors Collection, and maybe that would have had all of them in there?In which case perhaps you could do the RESTORE using OPENQUERY or one of the OA Method thingies, and maybe they "expose" an errors collection??Kristen |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 13:39:54
|
| Yea, not sure about all that...never done much of it.Another thing that could be done is to have a table that's inserted/updated each time a log is restored. That way you can use the table to know the exact file_id to pass for the next restore...hence no errors. But I didn't want to go that route having to maintain a table. Anybody know of a better practice to restore the logs when they are backed up to a single backup file (each hour)? Since log backups were ran every hour betwenn 8am and 5pm, there would not be anymore than 10 log backups in a day so I just wrote a RESTORE command in a loop and looped through it 10 times and incrementing the file_id I passed it by one each time. So 9 times it would fail but one time it would succeeed...always. Unless there was really an error...like the db was out of read-only mode or something. I thought about using the current time when the RESTORE ran to decide which file_id to use, but then there could be an instance when maybe the log backup didn't run for a hour or two for some reason and it wouldn't work to use the current time to decide the file_id. Any better way to do it...SQL2000 Standard Edition? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 14:31:21
|
| "so I just wrote a RESTORE command in a loop and looped through it 10 times and incrementing the file_id I passed it by one each time."And why not? !!!!!Processing the files in chronological order would probably help - assuming that a file wasn't missing."have a table that's inserted/updated each time a log is restored"That information exists in the MSDB on the Source machine. So might be able to be used on the Target machine too.Kristen |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 14:39:49
|
| What I didn't like about the looping is that it tried to apply each log...from 8am to 5pm each time it ran no matter what...and one would always get applied but the others would error. For instance, if it was 3:30pm and time to do the log restore, it would try to restore 8am log, 9am log, 10am, 11am, 12pm, 1pm, 2pm, 3pm, 4pm, and 5pm when all it needed was the 3pm log backup. But of course the way it was designed, there were few times I ever had to do anything to it. If there was a time log shipping got turned off, just turn it back on and everything would get restored. Would have just been nice to keep up with or be able to tell what actually needed to be restored and not have to try every log backup every time. But it worked great anyway. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 15:16:33
|
And I think that sounds fine. You and I both know that its a crap solution, but the Rolls Royce solutions are crap too ... but for different reasons. All these sorts of Administrator Fluff are crap. The better ones are crap-but-less-fragile-crap-nonethelessSo starting from that point:You want to copy some Backup files from A to B and restore them assuming:Some files will copy out-of-orderSome files may not copy e.g. file fails to copy [disk full] but later the file copies OK, because some other files copied OK, related to earlier Backups and thus have been restored and deleted - so disk space now exists for the subsequent file.I build my solutions trying to think of these sorts of Gotchas.Us Brits sent a probe to Mars a little while back. It was done on a shoe-string budget, but it got there, but then it failed to land. They reckon that the problem was that when it got there it handed over to the "landing" software and gave transferred control using Imperial units - but unfortunately the Lander was expecting Metric units (well, even if I've got that 100% round-the-wrong-way you get the picture!!)That's the sort of thing we are up against and I despair, no let me rephrase that! I DESPAIR of the sort of questions I see here where the OP is more than happy with fire-and-forget answers which assume that all will be well .. I have news for those naive posters: "All will NOT be well"!And whilst some of this is experience, it is also prudent to use "minimal technology". No fancy C# applications ... just copy the files, restore in a loop, don't delete anything until it has served its purpose [and then maybe keep it a little while longer - just in case!] ...... which is exactly how you describe your Log Shipping routine. Hats off to you; MS should buy your solution and make you a wealthy man. But they won't because you have failed to coble together lots and LOTS of COM objects, nor added Bloat, so your millionaire days are postponed I'm afraid.<sigh!>Did I miss anything?  Kristen |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 15:25:05
|
| LOL Can I get you to tell all that to Microsoft for me? If they make me a millionaire I'll send some of it to you. Tell you what. I've got 50 million USD that's sitting in an account of a deceased person. All I need is someone to help me get the funds out and I'll give you 20%. Just send me you bank account #, ssn (do they have those in britian), birthdate, name, and every other possible piece of info that would allow me to steal your money... Sad thing is, people fall for it sometimes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 15:46:55
|
"If they make me a millionaire I'll send some of it to you"<Sigh> If I had a billion-dollars for everyone who has promised me that ..  "I've got 50 million USD that's sitting in an account of a deceased person"yeah, yeah, yeah. But you haven forgotten to mention how uncomfortable those flights to Nigeria are ... <g> |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 15:55:01
|
| Hehe. I love 419eater.com That site cracks me up.I've gotten those emails about the X number of million dollars and you get 20% for helping. I got one about 3 weeks ago. I replied back that I was greedy and wanted 40% instead of 20% and all. Got him to email me back with pictures (cheap looking jpg's) that were documents from some bank that I needed to print, fill out, re scan and send to him. I never followed up after that....no time but it would be fun to mess with him more. Got his phone # too... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 16:10:51
|
| "I love 419eater.com"Know it well. But was too lazy to Google to quote it easily ... but I did have the thought to do so. Honestly!"I was greedy and wanted 40%"Hahahahahahahahahaha .. .Love It!!!!I love the 419eater.com story where they managed to get some minuscule grain of gold as a sample. One-up-to-the-good-guys!Kristen |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 16:14:22
|
| I like the ones where they do the wood carving and get some good "carvings" from the bad guys. I like the carving of the Commodore64. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 16:31:25
|
| Ah! the Commodore P.E.T. I could never bring myself to call it the "Commodore Pet" only ever the "Commodore P.E.T.". I had a North Star Horizon at the time; a real computer, by comparison. Cost me all the money that Granny left me when she shuffled off ... |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 16:42:22
|
| I had a Commodore 64 back in the day. I was proud of myself when I was about 10 y/o and got a math problem/program I was working on to save to a cassette. I had a cassette drive at the time because my parents couldn't afford a 1541 floppy drive for about $300 yet. I got one eventually though. I remember being in 6th grade (transferred in the middle of 6th grade from a Christian school to a public school and I had been at the public school about a week when this happened) and it was my turn to get to play on the Commodore 64 that was in the classroom. Hitting Ctrl-9 made the space bar leave a square behind. You could change the color too. I drew a house that way...no big thing...had done it dozens of times at home. When I was done drawing, I got up to go to my desk and when I turned around the whole class was staring with their jaws dropped. That would have been around fall of 1985. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-17 : 17:51:47
|
| I see my post has been read 60 times and only one person has responded..nice to know whe have people here that help...hehe |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-18 : 00:22:25
|
| Now don't you go deleting your posts on me ... |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-18 : 09:31:54
|
| Hi: |
|
|
|
|
Error handling multiple error messages