Find when SQL raw was created when no date column

Hi guys .
I have a table that contains all my desired data , but I want to select by date ....however no column of date in exist on the table....Can I filter my select by the time that raw "was created" ??

Thank :slight_smile:

Short answer: no. If you need to determine that later, you'd need to add a column like this to your tables:

ALTER TABLE myTable ADD logged_time datetime2(3) NULL;
ALTER TABLE myTable ADD CONSTRAINT myTable_logged_time_default DEFAULT(SYSDATETIME()) FOR logged_time;

FYI if you are adding this column to a table with existing data, you have to add the default as a separate step, otherwise all the existing data will seem to be logged on the exact same moment.

Longer answer: if you have ALL of your transaction log backups, and your database was in full recovery mode, you can look into the log backup files and tease out individual INSERT statements and get a date from it. When I say this is tedious, think of building a sand castle with tweezers. Personally, I would have to literally have a gun pointed at my head to try it.

If you'd like to learn more, here's an authoritative writeup:

Please read that THOROUGHLY, understand that it is UNDOCUMENTED, and all warnings that Paul lists. I've used that function to read logs but have encountered issues with it. I suggest copying transaction log backups to another, non-production SQL Server instance, and do the querying there. Paul doesn't describe the scenario you're asking about, I suggest you do some preliminary testing (create an empty database, create the table, query the log, insert a row, query the log again, see what's different, build a process from there)

FYI there are log reader 3rd party software that can do that latter suggestion for you. I've never used any of them, I know some were highly regarded in the past, sadly I've found they all now tend to spam heavily. I'm expecting some bot to spam this thread, so please be wary of any 3rd party data recovery/transaction log explorer.