What to do when a Database Delete happens in MSSQL

I recently had a situation where 3 database deletes happened with no one taking ownership of having done the deletes. Some important things that you should back up are:

• The Master database.
• The log folder for that instance of MSSQL. It contains some trace files (extension trc), the SQL event log files and the SQL error logs.
• All of the windows event logs

A useful SQL script to report on the log trace files:

With cteObjectTypes AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = ‘ObjectType’
),
cteEventSubClasses AS
(
SELECT
TSV.trace_event_id,
TSV.subclass_name,
TSV.subclass_value
FROM
sys.trace_subclass_values AS TSV JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id
WHERE
TC.[name] = ‘EventSubClass’
)
SELECT
I.StartTime,
i.TextData,
i.hostname,
i.DatabaseName,
TE.[name],
I.ApplicationName,
I.BigintData1,
I.ClientProcessID,
I.ColumnPermissions,
I.DatabaseID,
I.DatabaseName,
I.DBUserName,
I.Duration,
I.EndTime,
I.Error,
I.EventSequence,
Convert(nvarchar(10), I.EventSubClass) + N’-‘ + ESC.subclass_name as EventSubClass,
I.FileName,
I.HostName,
I.IndexID,
I.IntegerData,
I.IsSystem,
I.LineNumber,
I.LoginName,
I.LoginSid,
I.NestLevel,
I.NTDomainName,
I.NTUserName,
I.ObjectID,
I.ObjectID2,
I.ObjectName,
Convert(nvarchar(10), I.ObjectType) + N’-‘ + OT.subclass_name as ObjectType,
I.OwnerName,
I.ParentName,
I.Permissions,
I.RequestID,
I.RoleName,
I.ServerName,
I.SessionLoginName,
I.Severity,
I.SPID,
I.StartTime,
I.State,
I.Success,
I.TargetLoginName,
I.TargetLoginSid,
I.TargetUserName,
I.TextData,
I.TransactionID,
I.Type,
I.XactSequence
FROM
sys.traces T CROSS Apply
sys.fn_trace_gettable(CASE WHEN PATINDEX(‘%Log[_]%’, path)<> 0
THEN LEFT(path, PATINDEX(‘%Log[_]%’, path) + 2) +
‘.trc’
ELSE T.[path]
END, T.max_files) I JOIN
sys.trace_events AS TE ON
I.EventClass = TE.trace_event_id LEFT JOIN
cteEventSubClasses AS ESC ON
TE.trace_event_id = ESC.trace_event_id And
I.EventSubClass = ESC.subclass_value LEFT JOIN
cteObjectTypes AS OT ON
TE.trace_event_id = OT.trace_event_id AND
I.ObjectType = OT.subclass_value
WHERE
T.is_default = 1 AND
TE.NAME = ‘Object:Deleted’ AND
I.ObjectType = ‘16964’ /* This is DB */
order by i.StartTime desc