How to find deleted database?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 10, 2012 at 7:21 am

  #1431132

  I already did.

  GilaMonster (1/10/2012)

  ---

  http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 10, 2012 at 7:21 am

  #1431134

  I think Gail as already covered this.

  SELECT path from sys.traces

  --C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_671.trc

  If you don't see it on the folder maybe you don't have permissions?!!? I can't think of anything else.

• solomon.jernas

  Hall of Fame

  Points: 3680

  More actions

  January 10, 2012 at 7:24 am

  #1431136

  Thank you very much...

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  January 10, 2012 at 7:43 am

  #1431163

  solomon.jernas (1/10/2012)

  ---

  Pls Could u send the Example

  Execute the following against your SQL server instance to find the default trace path\file name

  select value AS DefTracePath from fn_trace_getinfo(1) where property = 2

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• solomon.jernas

  Hall of Fame

  Points: 3680

  More actions

  January 11, 2012 at 7:17 am

  #1431768

  I could not find one day before log data, i can see only current log file data.. who has deleted database..

• Geoff A

  SSChampion

  Points: 11511

  More actions

  January 18, 2012 at 2:35 pm

  #1435531

  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

  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 CHARINDEX('_', T.[path]) <> 0

  THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.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'