Post reply

Find user tables that have been updated in past week

  • February 25, 2009 at 12:14 pm

    #129257

    In SQL Server 2005 - Is there a way, without using triggers, to determine what what user table have been updated within the past week?

  • February 25, 2009 at 12:30 pm

    #950213

    When you say Update user table, are you talking about Data update or Table Alter?

    As GSquared once pointed out, SQL 2005 has a default trace that keeps track of Table Alters ct. But they are short lived.

    If you have Back ups, you could check it as well.

    -Roy

  • February 25, 2009 at 12:36 pm

    #950215

    Data update

  • February 25, 2009 at 12:41 pm

    #950219

    You will either need to have SQL Trace set up or you have to check Back up and compare. Or you should have a log table..:) If you have transaction log back up, you could do that as well. But I have never done that using transaction log.

    Thats all that comes to my mind. Maybe other have some other ideas...

    -Roy

  • February 25, 2009 at 12:43 pm

    #950221

    Thanks for your help

  • February 25, 2009 at 12:45 pm

    #950224

    Do your users have permission to run DML statements directly against the database without using an application interface? If so, you may be out of luck without utilizing a trigger than stores a system date and time in an auditing column like CREATION_DT or MODIFIED_DT. If the users access the db using a app then it is possible that the application handles the values assigned to such audit columns.

    Ken

  • February 25, 2009 at 12:47 pm

    #950227

    If you don't have an active auditing system (like triggers), and don't have a trace running, but do have the database in full recovery mode, you can get this data out of the transaction logs. You'll need a log parser for that, and there are several available. I believe ApexSQL still offers one, for example. Lumigent definitely does.

    If you don't have triggers, don't have a trace, and have the database in simple recovery, you can't get the data history. You can probably approximate it by restoring backups and comparing the table contents at the time of backup, but you won't have the full history.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 25, 2009 at 12:48 pm

    #950229

    Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply