November 15, 2008 at 12:45 pm
Hello Gurus,
I followed this article. http://www.sql-server-performance.com/articles/dba/Restore_Transaction_Logs_for_Point_in_Time_Recovery_p1.aspx
Link for the forum from SQL-Server-Performance
http://sql-server-performance.com/Community/forums/t/28721.aspx asking how do you get time stamp for the particular table drop.
I got answer from the SQLSERVERCENTRAL and I even recovered to the point in time.
Now if you check the 2nd link one of the replies in that is
This is just an example with time stamp given and its just for our understanding. In realtime scenario if somebody accidently deletes any table then the first thing we need to do is if possible try to get the time of the table or object deletion from the concern person and then on a standby server do point n time recover and copy that object back to production.
So my question is does it really work like that in the production environment? You asked person who dropped table or any DB Object and ask them to guess time?
Pls reply, your reply would be highly appreciated.
Thanks
November 17, 2008 at 9:20 am
That's one way to determine when the incident occurred. If you have some sort of auditing in place or have a DDL trigger that records when a table is dropped, you could rely on that. If you have a T-Log reader like Lumigent Log Explorer or ApexSQL Log, you could pinpoint when the table was dropped.
Greg
November 17, 2008 at 9:28 am
Thanks for the reply Greg.
November 17, 2008 at 9:52 am
In SQL Server 2005 and greater, there is a default trace that is enabled. You can search the default trace for the creation, alter or deletion of an object.
The event class in the trace will be 46 for creation and 47 for deletion (I think). Review Books Online on reading trace files for further information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 17, 2008 at 10:06 am
Thanks Jeffrey. That's how I figured it out when I dropped the object using default trace files created by the SQL Server.
November 17, 2008 at 12:13 pm
Jeffrey Williams (11/17/2008)
In SQL Server 2005 and greater, there is a default trace that is enabled. You can search the default trace for the creation, alter or deletion of an object.
Of course. Good call Jeffrey. I forgot about the default trace.
Greg
November 17, 2008 at 1:19 pm
Remember, the trace is recycled based on the transaction level of you SQL Server. So you might end up filling up the trace in 1/2 a day or even 2 days.(Correct me if i am wrong)
select * from ::fn_trace_gettable ('Trace path and name',default)
Maninder
www.dbanation.com
November 17, 2008 at 1:36 pm
Mani Singh (11/17/2008)
Remember, the trace is recycled based on the transaction level of you SQL Server. So you might end up filling up the trace in 1/2 a day or even 2 days.(Correct me if i am wrong)select * from ::fn_trace_gettable ('Trace path and name',default)
You're right !
But by default it keeps 4 default traces until it drops the oldest one.
So best is to:
- double check that the default trace is running (do it NOW !)
- at the time you need it , first copy all your trc files so a rollover nolonger bothers you.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 18, 2008 at 9:08 am
SSC has an article recently covering the Default Trace
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply