One day we found that user has reported that we should have one database “XXX” to be on the server, we checked the error log and found that the database “XXX” is getting restored daily. But when we check on the server we could not see the database exists on the server.
Means we could detect that someone is restoring the database but database is not exists. Error log keeps record of restore only, to find out how the database is dropped or detached we need to use the default trace, which is introduced in sql server 2005 and enabled by default.
This trace flag keeps little information about the database activity and creates .trc file at the default location where your Error log file exists can be get path from table sys.traces and id =1 means default trace, and to read the trace file use function fn_trace_gettable, its good practice to insert the trace data into temp table to manipulate the output per requirement.
The standard script would be
DECLARE @path varchar(256)
SELECT @path = path FROM sys.traces where id = 1
SELECT * into #defaultT FROM fn_trace_gettable(@path, 1)
Select textdata,starttime,targetusername from #defaultT
Analyse the data where you can find who/when the data is detached or deleted
and to know the object we need to read the transaction log
SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = ‘DROPOBJ’
[Update 03 Dec 2012]:
Paul has written another blog on how to find the same if tlog is inactive using another undocumented function fn_dump_dblog how to concert LSN format.
other good alternative is pre-planning using
DDL Trigger would be another good option.