CODEWITHSUNDEEP
sqlsql-server-2008
slash shogdhe
slash shogdhe

Reputation: 4197

How to track deleted database

Suppose my sql server 2008 with username(sa) and password(testpass),with a instance(mypc).

i am having two computer name system1 and system2. If i am deleting the any database then

how can i track from which system this desired database is deleted.

Upvotes: 0

Views: 698

Answers (2)

Anthony Faull
Anthony Faull

Reputation: 17997

You can set up a server audit to monitor all CREATE/ALTER/DROP DATABASE events.

CREATE SERVER AUDIT [DatabaseChanges]
    TO APPLICATION_LOG;

CREATE SERVER AUDIT SPECIFICATION [DatabaseChangesSpec]
FOR SERVER AUDIT [DatabaseChanges]
    ADD (DATABASE_CHANGE_GROUP);

Upvotes: 1

Shan Plourde
Shan Plourde

Reputation: 8726

Interesting question, I've never run into this type of scenario before. What problem are you trying to solve?

You could monitor the filesystem where the database files are stored. Perhaps a command line application, or Windows service. When it detected database files to be deleted, you would then know.

Alternatively, from the client connection, you could try to connect to the database. If the login succeeded but database schema was not found, you could assume that the database had been deleted.

Upvotes: 0

Related Questions