How to retrieve the datetime when one row was inserted/updated in Sql Server 2005?

Question

Hi,

Is there a way to find out that info? Maybe in some kind of internal log in Sql Server? I need to generate a report based on historical data, but I haven't implemented that feature in my app, as it was not a requirement in the past. Now I'm stuck.

Thanks

Answer 1

Sorry Charlie. If it's a web app you might be able to produce a report based on that log.

Answer 2

You have to add a column to the table (make it nullable) and then seed all the rows with getdate(). This isn't accurate of course but it's your only option. Once all the rows have data, add the NOT NULL constraint to the column.

Answer 3

There may be a slight chance that transaction logs are kept and you are log shipping. Check with your DBA.

Those logs could provide you with the date/time stamps you are looking for.

Answer 4

SQL Server does not have automatic and complete auditing of all data changes.

You have to build it in. Whether it was in the original requirement or not, it's common sense to track when stuff happens.

Your best bet is to add this, and accept that data prior to the new feature has no tracking.

Answer 5

you may get lucky being able to get the data from some log, but you need to prepare for the future. No matter what the specs say, it is a good idea to add some extra info and logging.

In really important tables, I have a history table with every version of the row, including LastChgID and LastChgDate.

In important tables I have CreateID, CreateDate, LastChgID, LastChgDate

In all other tables (other than codes tables) I have LastChgID, LastChgDate

when you design the system, you'll have an idea of what is important.

Many of this is displayed on the screen, but even if it isn't, some upset person will call/email who do this? and you can say "you did an 2009-06-02 10:06:22.213!"