SQL Server Performance Forum – Threads Archive
Any solution to this question?
Hello DBA’s I just wanted to know about the solution to the following scenario: Somebody has deleted a database (by securing the password somehow). Is there anyway we can know who has done that and when has that happened? ThanksSatya
You can use third party tools like Log explorer, If you have the transaction log backups…
MohammedU.
Moderator
SQL-Server-Performance.com
I dont think third party softwares can help you recover the database, because the log files also deleted with the database. But you may be able to find the when and who part by analysing the log file of the master database. Apex SQL has a neat log analyzer, recovery tool. http://www.apexsql.com/sql_tools_log.asp
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
What I was saying was third party tools can be used to analyse the tlog backups to find who and when the db was deleted but not for recovery…
Recovery can be done only when you have FULL and Diff/tlog backups…
MohammedU.
Moderator
SQL-Server-Performance.com
Looking at Transaction Logs is one way of understanding as to what caused deletion of database. However proactively you can set "C2 auditing" which traces Audit Events. Certain Events like Object Dropped can be monitored and if it is SQL 2005 you can get the Tracing on the default log directory automatically. There are tools like APEXSQL and Lumigent which does a good job of Log Explorer. Sumit sarkar
IF it has happened already then third party tools are only way to know who has done it.
If not then you can deploy the C2 auditing, but bear in mind to keep the disk size in order to accomodate this suddent increase of file sizes. But in any case you need to test the implications rather than simply affecting them on the production serve. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Proactively checking who is dropping db can accomplished by sql alerts without using C2 audit…As you know C2 audit is expensive.. Check the following script to create drop db alert… — CREATE "DATABASE DELETED" ALERT:
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N’Database Deleted’))
EXECUTE msdb.dbo.sp_delete_alert @name = N’Database Deleted’
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N’Database Deleted’, @message_id = 3738, @severity = 0, @enabled = 1, @delay_between_responses = 0, @notification_message = N’See Event Viewer -Application log.’, @include_event_description_in = 1, @category_name = N'[Uncategorized]’
EXECUTE msdb.dbo.sp_add_notification @alert_name = N’Database Deleted’, @operator_name = N’SQL_DBA’, @notification_method = 1
END
GO
exec sp_altermessage 3738, ‘WITH_LOG’, ‘true’
GO MohammedU.
Moderator
SQL-Server-Performance.com
another way is DDL Trigger. Try this DDL trigger to keep a log of any database Alter/Create/Drop activities. CREATE TABLE dbo.DDLAttemptLog
(EventData xml NOT NULL,
AttemptDate datetime NOT NULL DEFAULT GETDATE(),
DBUser char(50) NOT NULL)
GO CREATE TRIGGER srv_trg_TrackLoginsActivity
ON ALL SERVER
FOR CREATE_Database,ALTER_Database,DROP_Database
AS
INSERT Master.dbo.DDLAttemptLog
(EventData, DBUser)
VALUES (EVENTDATA(), USER) –creating a test database
create database testtrgdb
— Checking the log table
select *from DDLAttemptLog Drop database testtrgdb select *from DDLAttemptLog
Madhu
]]>