Databases disappear | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Databases disappear

Hello everyone;
On one of my box I found Monday morrning that one of my database disappear; I restored it from last backup but I want to know who delete it or why it disappear. I checked server log which shows it started on Saturday night but did not found any entry for deletion. Is there any way to retrieve information about deletion of a database.
or SQL server is built in a way not to record such information. any idea please……….
Ashfaq
I don’t think there any way other than sql trace…
In sql server 2005 sql runs backend trace and writes the files to C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG location so if you have those file you may get some info from them…otherwise you are out of luck…
Mohammed U.
Are you logging on with the same credentials?
quote:Originally posted by MohammedU I don’t think there any way other than sql trace…
In sql server 2005 sql runs backend trace and writes the files to C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG location so if you have those file you may get some info from them…otherwise you are out of luck…
Mohammed U.

I think since it is SQL 2005, what you should do is create DDL Triggers on Server Level to track these kind(who/when created/deleted this DB/login etc) of activities. Madhu
i am logging with same "sa" credentials; when i check for this otherwise i am using different account. Trace you can use when you looking for something that will happen in future. and also you can’t run trace continously; because it put extra burden and generate huge file in couple of minutes in heavy use boxes. any other idea please……. Ashfaq
why don’t u try DDL trigger? the sole purpose of DDL trigger is these kind of audit only Madhu
For such things SQL server error log is the first and best to look out, that gives you high level information and if you are really worried about such things then you have to tighten the security on database. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
i will try to come up with DDL triger but if you or anyone have any format or syntax please post it that we all get the advantage. Ashfaq
Refer to the books online for SQL 2005 on code examples in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
there is an another way, try Windows Management Instrumentation Alerts. define a WMI alert you need to know how to write WQL queries. if u can do r&d on this and share the knowledge it would be great Madhu
[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Even BOL has such section<a target="_blank" href=http://msdn2.microsoft.com/en-us/ms180524.aspx>http://msdn2.microsoft.com/en-us/ms180524.aspx</a> &<a target="_blank" href=http://msdn2.microsoft.com/en-us/ms186371.aspx>http://msdn2.microsoft.com/en-us/ms186371.aspx</a> as a warmup.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
Try this DDL trigger and AuditTrail procedure……. Step 1…. Create a AuditTrail table CREATE TABLE <DatabaseName>dbo.DDLAttemptLog
(EventData xml NOT NULL,
AttemptedOn datetime NOT NULL DEFAULT GETDATE(),
AttemptedDBUser char(50) NOT NULL) Step 2…. Create Server Level DDL Trigger Alter TRIGGER srv_trg_AuditDatabaseActivity
ON ALL SERVER
FOR CREATE_Database,ALTER_Database,DROP_Database
AS
INSERT <DatabaseName>.dbo.DDLAttemptLog
(EventData, AttemptedDBUser )
VALUES (EVENTDATA(), USER) If u want to rollback, then do accordingly in the trigger body. Also replace the <DatabaseName> with your databasename. Madhu
i am tring to use your store procedure but encountering with some problems. first error is "Cannot find data type XML". this error is from ruinning first step. when i run second part it give me error ‘Eventdata’ is not a recognized function name. i am running it over SQL 2000. am i doing someting wrong or there is some problem in the above code?
Also did you tried it at your test box.
thanks for posting Ashfaq
xml data type is new in 2005 it is not in 2000 and I believe EVENTDATA function also the same… http://msdn2.microsoft.com/en-us/library/ms173781.aspx
http://msdn2.microsoft.com/en-us/library/ms189887.aspx
Mohammed U.
Hi Ashfaq, u posted this under SQL 2005 forum thats why i didn’t mention that. This will work only 2005. DDL is one of the New feature in SQL 2005. as mohammed told, XML datatype and EVENTDATA() functiona also not available in 2000. Madhu
thank you all but if any one has any idea how we can get around this problem in SQL 2000 please e-mail me at [email protected]
thank you.
Ashfaq
THere is no such available as mentioned you have to depend upon the PROFILER (Server side trace) or use third party tool to audit the SQL Server. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Most of the 3rd party tools also uses sql trace so why don’t use create your own trace without paying<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br /><br />Mohammed U.
]]>