Data is deleted

Posted on 2009-12-18
Last Modified: 2012-05-08

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.

Question by:srinivas_ganamur
    LVL 20

    Accepted Solution

    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:

    LVL 20

    Expert Comment

    by:Marten Rune
    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!


    Author Comment

    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.
    LVL 20

    Expert Comment

    by:Marten Rune
    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.


    Author Closing Comment

    Forced to close the question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Setting up a Microsoft WSUS update system is free relatively speaking if you have hard disk space and processor capacity.   However, WSUS can be a blessing and a curse. For example, there is nothing worse than approving updates and they just have…
    Learn about cloud computing and its benefits for small business owners.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now