July 2, 2008 at 3:23 am
I have a database which stores diagrams. The users called saying that a number of diagrams had 'disappeared' and they needed the previous nights backup restored. This was done but I backed up the database and log first.
I have now restored to my local machine and want to interrogate the log for 'delete' or 'drop' commands. Any ideas where I start? Seems a simple task but I have just never had to before, I can see there are tools but company policy prevents this!
So all i have is SQL 2005. Help!!!
All suggestions greatly appreciated.
Adam Zacks-------------------------------------------Be Nice, Or Leave
July 2, 2008 at 5:15 am
There is an undocumented DBCC command "DBCC LOG" that will return transaction log information, but since you are working from a restored database I am not sure if it will be able to tell you anything. You could try it.
July 2, 2008 at 5:27 am
I don't know of any functions within SQL to read a backued up transaction log. all the ones (DBCC Log, fn_dblog) read the active portion of the log, not a backup of it.
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
July 2, 2008 at 5:36 am
Yes, it has to be the active log, but I think if you restore a database and then restore the log file, the "replayed" transaction log entries show up. I just don't have an environment to try it right now.
July 2, 2008 at 5:53 am
Yes, your're right it should.
I cvan't test now, but I have seen it in the past on a restored database.
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
July 2, 2008 at 5:56 am
Hey guys thanks for your replies.
Ok, so next problem, when I run the 'dbcc log ("SA-restored", type=3)' command i only get the recent transactions (since it was restored).
So what I need to do is delete the restored db i have and restore it again in a way which will not apply any commands in the log or truncate it.
Any ideas?
Adam Zacks-------------------------------------------Be Nice, Or Leave
July 2, 2008 at 5:58 am
Can you suggest how I can restore the two backups (db and trans) without having them redo or undo or truncate?
Adam Zacks-------------------------------------------Be Nice, Or Leave
July 2, 2008 at 5:59 am
You need to restore the backup you took before restoring the previous night's backup. Restore that somewhere and interegate the log.
The database that you restored to the previous night's backup will only have transactions that occured before that backup.
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
July 2, 2008 at 6:07 am
Yes, this is all assuming you have a regular full backup and transaction log backups in between.
Don't restore last night's back - restore the latest full backup BEFORE the records were deleted and then apply transaction logs until you have passed the deleted records. I think DBCC Log will then be able to show you the transactions you want to see.
July 2, 2008 at 6:09 am
Let me add that I have never tried to do this. I just think it will work.
Of course, if Gail and I both think it will work, the odds are pretty good.
July 2, 2008 at 6:12 am
Or we're both delusional, which is at least possible... 😀 😉
Considering I'm still up on cloud 9, it's not so unlikely for me. :w00t: :w00t: :hehe:
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
July 2, 2008 at 6:16 am
True, but what are the odds we're both off our meds on the same day?
July 2, 2008 at 6:21 am
Michael Earl (7/2/2008)
True, but what are the odds we're both off our meds on the same day?
Good point.
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
July 2, 2008 at 7:04 am
Alternately, download the free trial of ApexSQL's log parser, and use that. It can work on active log files as well as reading from backups, etc. I think it has a 30-day free trial, with full functionality. That should get you what you need pretty quickly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 7:22 am
Thanks for yoru help.
I have restored the db to'SA-restored' on my local SQL server. This was restored from a backup yesterday (after the deletions occurred). I then restored the trans backup i took at the same time yesterday.
Then ran 'dbcc log ("SA-restored", type=3)' and only get results that have occured since the restore.
Yesterday I got 160k rows. So have I missed something or do i restore differently.
I did use the 'dbcc log (DBNAME, type=3)' command yesterday but didnt have time to reveiw becuase the users needed data restored.
Appreciate your continued help.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply