Way to find the deleted database
-
Hi,
Is there any way to find which user has deleted a database if there was no trace taken during that time. I cannot find the same from default trace.
Ryan
//All our dreams can come true, if we have the courage to pursue them// -
I think the default trace contains all the information you need:
http://sivasql.blogspot.com/2010/07/how-to-find-who-dropped-database-is.html
Hope this helps
Gianluca
-- Gianluca Sartori
-
It'll be in the default trace, but that could have rolled over (it's only 5 files of 20MB). If the activity is such that the drop has been cleared from the default trace, then there's no way to tell who did the drop.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (7/7/2011)
It'll be in the default trace, but that could have rolled over (it's only 5 files of 20MB). If the activity is such that the drop has been cleared from the default trace, then there's no way to tell who did the drop.Nice catch, Gail.
@Sumanta: any clue on when the database was actually dropped?
-- Gianluca Sartori
-
Thanks Gianluca Sartori and Gail
The database was deleted today and found the same from schema change history report. But unfortunately I cannot get it from the default trace. I ran the below query giving the correct path for default trace as mentioned in the link. The textData does not show any drop database statement.
http://sivasql.blogspot.com/2010/07/how-to-find-who-dropped-database-is.html
select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
Is it the right query I am running?
Ryan
//All our dreams can come true, if we have the courage to pursue them// -
The default trace location can be found issuing this:
SELECT value
FROM :: fn_trace_getinfo(default)
WHERE traceid = 1
AND property = 2
To see the first available entry in the trace you can run this:
select MIN(startTime)
from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
What timestamp does this return?
-- Gianluca Sartori
-
Sumanta Roy (7/7/2011)
Thanks Gianluca Sartori and GailThe database was deleted today and found the same from schema change history report. But unfortunately I cannot get it from the default trace
The schema change history report reads from the default trace, so if it's in there it's in the default trace.
Make sure when you run the trace_gettable query you replace the file location and name with the one returned from sys.traces.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It returns 2011-06-28 10:05:18.113
I ran the below query where log_35.trc is my default trace
SELECT * FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_35.trc',default)
where TextData like '%drop%'
--I cannot find any drop statement here.
Ryan
//All our dreams can come true, if we have the courage to pursue them// -
Any matches for the database name?
-- Gianluca Sartori
-
No you won't, because the default trace doesn't capture any of the T-SQL events, so there's no T-SQL commands in the textdata column. You're looking for specific events that are fired for drop database (actually, for drop object).
Have a look in Books Online for the page on sp_trace_setevent, the event classes are listed there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
No, I dont find any proper match for the database name.
Ryan
//All our dreams can come true, if we have the courage to pursue them// -
Sumanta Roy (7/7/2011)
No, I dont find any proper match for the database name.Take a look at Gail's suggestion, then.
-- Gianluca Sartori
-
Sumanta Roy (7/7/2011)
No, I dont find any proper match for the database name.What column? It is not going to be in the text data column for the reasons I mentioned. For most events in the default trace the textdata is NULL because there's no associated text.
Look at the other columns in there. To start, just do a select * from fn_.... so that you can see what is in there and what's in what column. Otherwise you're doing the equivalent of searching a mansion with a penlight instead of switching the main lights on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
you could try using the built in Schema Changes Report in SSMS;
instead of a specific database, right click ont he server itself and browse to schema changes history
look for the DDL Operation "DROP" in the report.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply