September 9, 2009 at 5:12 pm
Hi,
Incidentally someone of my dev database deleted and I don’t have any backup for that, I am looking around to trace in which login that database is deleted. Is there any script or anything to know?
Thanks
Manas78
September 9, 2009 at 10:21 pm
A default trace is enabled by default on sql 2005 servers. Check out for traces that it generates. It should contain database drop information.
September 10, 2009 at 7:38 pm
Here is a query that I've used to query the default trace. To use it, run this statement to get the path of the current trace, and replace 'PATH' in the larger query. You will need to put your database name in this line: AND DatabaseName = 'DropTest'. Depending on how long it ago it happened, it may be in an older trace file. To view older trace files, you can decrement the number of the trace file. For example, on my system the log file name was log_184.trc. To view the previous log, it would be changed to log_183.trc. I hope this helps.
/*
The default trace functionality provides a rich, persistent log of activity and changes
primarily related to the configuration options
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1322d668-44f4-469e-8fd6-e0d02a81c8f2.htm).
--Get the current trace rollover file(Plug the path in the query below)
SELECT * FROM ::fn_trace_getinfo(0)
*/
SELECT
StartTime
,d.NAME
,tei.NAME AS EventType
--,o.NAME
,TextData
,tg.ObjectName
, NTUserName--, BinaryData
,'--------'
,tg.*
FROM fn_trace_gettable
('PATH', default) tg
INNER JOIN sys.Trace_Events tei ON tg.EventClass = tei.trace_event_id
LEFT OUTER JOIN sys.databases d ON tg.DatabaseID = d.database_id
--LEFT OUTER JOIN sys.Objects o ON tg.ObjectID = o.object_id
WHERE DatabaseName = 'DropTest'
AND tei.NAME = 'Object:Deleted'
--AND TextData IS NOT NULL
--AND ObjectName = ''
--AND NTUserName = ''
--AND TextData LIKE ''
ORDER BY tg.StartTime
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply