Last added records in SQL-Server table

Question

Is it possible to see when a record has been inserted in a SQL table without using a datetime column?

Answer 1

yes if the dbms you are using has a log-function and you have enabled that one. then you can query the system-log-table. oracle has got something like that which is enabled per default. if you are using sqlserver I think you have to enable the logging-mechanism.

Answer 2

no. sql server doesn't have this functionality out of the box.

Answer 3

If your using SQL Server 2008 you can look into Change Data Capture which is a new auditing capability that I'd imagine would have this.

Another possibility is to put an identity column on the table. Then you can just order by the column descending.

You can also try the DBCC Log command. I don't know if this will give you what you want, it might take some work to get it to a point where you can interpret the data it gives you.

Answer 4

The only way I know of is to restore your backup + transaction log to a point in time. Then you can check for the rows existence, wash, rinse, repeat. If this is sql 2000, I believe there are some log parsers out there so maybe they could help you locate at what point in the log a row was committed. If it is sql 2005/2008, I don't think there is a log parser yet.

Answer 5

For SqlServer you would nead a sql log viewer/analyser such as the one in the Red Gate suite of tools.

Answer 6

IF you want to see meta data such as date inserted or updated you need to set this up. The easiest way to see the inserted date is to add a date inserted column. The more difficult way is to add auditing (which frankly every production system that contains business critical information should have anyway). This can involve triggers or an outside auditing system or change data tracking in SQL Server 2008, but none of those methods are set up automatically and you cannot use them to look up data added before you set up a a system. If someone recently added a record, you might be able to tell when it was added from the transaction log using a tool that reads the logs, but this an expensive way to go usually and it still wouldn't tell you who added the record which most auditing solutions would.