• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Data is deleted

Hi,

In my current company, some one is deleted data in table. I dont who is delted. I need to find last one month DML/DDl operations in my server/database. So please give me t-sql code to find out last one month operations in serve/databaser with host name (coz all are using sa login)

Please reply ASAP.

Regards,
Sri
0
srinivas_ganamur
Asked:
srinivas_ganamur
  • 3
  • 2
1 Solution
 
Marten RuneCommented:
This is almost impossible to trace in your case. Your setup seems to be worst practice im sad to say.

When this is said, lets look at your question. Is your objective to restore repair the data thats been deleted?
If so check out your backups. Do you know when the data was deleted, i e some important system started to malfunction? If you have a specific time AND the database is in FULL recovery mode, you might be able to do something with a couple of 'Point in time' restore. One before the troupbles, and one after, restored as other dbnames, perhaps on another server. By comparing these you might be able to minimize the data loss.

Is your objective to find a culprit?
If so, you only have one chance as I see it. There is a default trace, that starts automatically in a standard set up SQL (SQL 2005 or later this is). If your data was lost by a object being destroyed, i e drop table. Then you can find some information in default trace. A new default trace is created each time SQL Server is started, and the server deletes old traces that is obsolet. You will find the last 5 default traces in your log directory.

For information about Default Traces: http://deepakrangarajan.blogspot.com/2008/12/sql-server-2005-default-trace.html


//Marten
0
 
Marten RuneCommented:
As another remark.

you should really redesign your SQL Solution. Look at security aspects, best practise. And maintenance for your databases. Do you backup system databases, user databases, rebuild/reorganize indexes, take log backups, check database integrity and soforth. I feel this is needed to be said in this thread. So there it is!

//Marten
0
 
srinivas_ganamurAuthor Commented:
in this i am not recovering deleted data coz it is development server. My question is i want to see what are the commands (DDL/DML/Alter procedure) executed from past 0ne month.
0
 
Marten RuneCommented:
In my opinion (my fellow expert colleagues, please validate/reject this post) this is not possible.

As stated above you can get information about DDL from default trace, but other than that you need to put monitoring in place BEFORE the statements you want to examine/capture. This is because a standard system should not loose capacity due to excessive monitoring.

//Marten
0
 
srinivas_ganamurAuthor Commented:
Forced to close the question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now