Reading transaction logs... 1

[bessebo]

I have an issue that I need to deal with. We had a problem over the weekend where we lost two drives in our Raid 5 array on Saturday early evening and these drives were our database for our ERP system. I had a full SQL backup from the night before on another drive that I was able to restore. I also run transaction log backups throughout the day during the week and on Saturday. Typically nothing happens at my company on Saturdays but this Saturday there were some inventory transactions made that I found out about on Monday afternoon (2 days after the failure). At that point it is too late to apply the transaction logs from Saturday. I know I should've tried to apply the transaction logs from Saturday but I was just really happy that our full backup from Friday night restored successfully. Anyway, as it turns out, our network guy made a mistake in the tape backup routine and what happened is that our full backup from Friday night was not written to tape. So I ended up saving off the transaction logs from Saturday but don't have a full backup from Friday night. If I did I could restore that full backup to another server and apply the tranaction logs up until the point of failure. My question is, is there any tool out there that I could use to read the transaction log that I have saved off? I'm pretty sure that I cannot apply the transaction logs from Saturday to a full backup of the previous Thursday night because there was a whole day's worth of transactions that occurred on Friday. Any advice would be helpful. There were minimum transactions made on Saturday but my boss would like to at least have the information.

Thanks,
Bessebo

 

[SQLBill]

Do you have the transaction logs from Friday? If so, restore the full backup from Thursday, then the logs from Friday and Saturday.

If not, I think you are out of luck. While I don't use any of the 'log explorer' programs, everything I've seen leads me to believe you have to have had it installed prior to the problem. In other words, they can only read logs created after the log reader program was installed.

But as I said, I've never used any of them. I believe the Lugiment Log Explorer trial version only allows you to read the logs for the Northwind and Pubs dbs, so that won't help you test it.

There's something called Log P.I. (at least I think that's the name), check out them and Lugiment online and see if either say they can read logs created before the program is installed.

-SQLBill

Posting advice: FAQ481-4875

 

[bessebo]

Bill,
I don't have the transaction logs from Friday because when I perform a full backup each night, and it is successful, I end up initializing the transaction log since there is no need for it. If the full backup is not successful, I do not initialize it. Our network guy admitted to screwing up and what happened is that the successful full backup from Friday night was overwritten on the tape with a subsequent tape backup. It makes me tend to re-think my backup strategy so I can account for mistakes such as the above. I just wish I applied the transaction logs after I restored and this would not be an issue. To be honest usually nothing happens here on Saturdays but this weekend a few transactions were made. That is why it would be nice to see exactly what serial numbers were scrapped (there were about 500 serial numbers scrapped with these transactions and the product was thrown in the dumpster and compacted). So we don't even have the serial numbers. If I could read the transaction log I could get the information. I might look into those applications that you mentioned. I was just wondering if anyone out there has used them. Right now seeing the transactions would really help us. FYI, I am not in any trouble but our network guy could be. Did you enjoy PASS? I was there and should've tried to meet up with you. Where did you stay?

Regards,
Bessebo

 

[SQLBill]

I was at the Hyatt Regency. PASS 2005 was okay, nothing great. I didn't like the Welcome Reception - to me a welcome reception is for the members. This was for the SIGs, they played that game and won prizes - the members weren't involved at all (and there wasn't enough food). Some of the sessions were awesome, but some were listed as 3xx level and were really entry level. That was disappointing. I wasted my time in a session on how to create a trace (listed as a 3xx level - experience needed). It was advertised as how to set up traces and understand their results. None of that was discussed and the instructor kept having to look up info in the BOL. This was my first PASS and would be my last, except I heard from several people that they had been to several and this was the worst one ever. They suggested trying it when it's at Seattle or Florida. So I may try again. I learned a few new things....just not enough to make the cost worthwhile.

-SQLBill

Posting advice: FAQ481-4875

 

[bessebo]

It was also my first PASS meeting. It may also be my last. I really didn't know anyone there and I went by myself so it wasn't a blast for me. But, I was there to learn anyway. Did I learn much? Not a whole lot. There were so many sessions on SQL 2005 and we will probably not move there for another year or so. A lot of these guys were testing the latest customer version of 2005 and were really into it. I don't have the time to do that, in fact, we just recently moved to SQLServer 2000 on a Windows 2003 server. I stayed at the SpringHill Suites and I really liked it there. I signed up late anyway. We use Epicor for our ERP system and since I went to the PASS meeting I couldn't go to the Epicor meeting which is at Caesar's Palace in Vegas. I went to that one last year and it was fun and I did learn a decent amount as well. I'll probably skip PASS next year and go to Vegas.

Can you suggest any local SQL User's groups that are worthwhile attending? Here I am assuming that you live in the Mass, NH area because that is where I am.

 

[SQLBill]

I live in Maryland and don't know of any User's groups in this area, nor in yours. I personally wouldn't have time to attend anyways. I'd like to recommend another tech site,

http://www.sqlservercentral.com.

They are specifically SQL Server and I believe there might be a forum for user's groups there.

Both that site and this are great sites....each one compliments the other and are for specific things. This site is for computer pros in general and has great help for SQL Server. The other site is specifically SQL Server and has articles, scripts, Question of the Day and other features targeted at SQL Server.

Personal opinion here: I prefer this site for getting answers and the other site for the SQL Server articles and how-to's.

-SQLBill

Posting advice: FAQ481-4875

 

[bessebo]

Bill,
I get the SQLServercentral.com emails every day and have seen your posts on there. In fact, I even entered their code when I registered for PASS so I could attend their party on Tuesday night. Ended up flying in on Tuesday night and was too exhausted to attend. I agree. There are excellent articles on that site but this one has more answers...

Thanks,
Bob

 

[SQLBill]

This thread has gotten off topic and your question never did get answered.

I suggest you start a new thread, titled "Log Explorer, Log PI Advice wanted" or something like that. And then just ask "I need to be able to read a log and have heard of some products that allow you to do this. Does anyone have any experience with log reader programs? And if so, can you tell me your experience?" Then link this thread to the new one. (type T H R E A D 9 6 2 - 1 1 4 2 4 1 0 without the spaces).

-SQLBill

Posting advice: FAQ481-4875

 

[bessebo]

Bill,
Thanks. As it turns out, the activity that was performed on Saturday was too miniscule to deal with at this point. It would be interesting to look into those tools though.

Thanks,
Bessebo

 

[grimwyre]

We used Log Explorer to read backup TLogs generated before its installation. But it can't identify who initiated the change. It could also possibly be used to undo or redo the changes, though that is easier if done close to the change. We ddn't try that. Log Explorer would have to be licensed for your database.