Time for record Insertion

Question

Time for record Insertion

newbieuser

SSCrazy Eights

Points: 9423
More actions
April 8, 2008 at 11:52 am

#121381

Hi Experts,
I am trying to find a method to find out the time when a few specific records were inserted into the table. Is there a way to find the time??
Thanks much!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 1

One way, if its possible is to add InsertDate in table and default that column as getdate().

Second option is to store the getdate() value in some variable and basically flush that variable value to some other detail table

where you have comments and insertdate as col.

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Thanks Sameer. Thats a good option in future. But I actually need to get the list of records got inserted from august till now. Is there a way I could find that? Thanks

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 3

If you have not made any arrangement to store the date previously,

sql server will not store insert date/time for previous records.

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

CodeLake SSCrazy Points: 2082 More actions · Answer 4

As Sameer explained it's not possible but in future (also suggested) you should have Auditing fields in each table.

I normally have 4 fields in each table.

[UserCreated] [varchar](16) NOT NULL

[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_TableName_DateCreated] DEFAULT (CURRENT_TIMESTAMP)

[UserUpdated] [varchar](16) NOT NULL

[DateUpdated] [datetime] NOT NULL CONSTRAINT [DF_TableName_DateUpdated] DEFAULT (CURRENT_TIMESTAMP)

Kindest Regards,

WRACK
CodeLake

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 5

newbieuser

SSCrazy Eights

Points: 9423

April 9, 2008 at 6:32 am

#800306

Thank you very much.

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 6

One way to do this could be - assuming you have the transaction logs (or can recall the tapes/files), you might use a tool like Lumigent Log Explorer or Redgate's SQL Log Rescue to inspect the transaction logs. You should be able to see the transactions that happened on the required table.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Girish Bhat (4/9/2008)
One way to do this could be - assuming you have the transaction logs (or can recall the tapes/files), you might use a tool like Lumigent Log Explorer or Redgate's SQL Log Rescue to inspect the transaction logs. You should be able to see the transactions that happened on the required table.

Of course - you'd need to have 10 months worth of Transaction log backups still available...:) I'd leave that for truly desparate measures. Navigating the transaction logs is mind-numbing stuff.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

Girish Bhat (4/9/2008)
One way to do this could be - assuming you have the transaction logs (or can recall the tapes/files), you might use a tool like Lumigent Log Explorer or Redgate's SQL Log Rescue to inspect the transaction logs. You should be able to see the transactions that happened on the required table.

Have you ever tried that one. I doubt. It is very tedious and time consuming factor. If you have time and resources then go ahead .......

🙂

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 9

Matt - Depending on the situation it may not be necessary to go through 10 months of log. Example: If it is a table in which data is only being added, something like an audit table, you just need the logs of August to see what was the first record added. 10 months is not atoo long a time to preserve tapes, specially in industries like financial services. Thats why i said - "assuming you can recall the tapes/files". But I do agree this solution may be for desparate situations.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Girish Bhat (4/9/2008)
Matt - Depending on the situation it may not be necessary to go through 10 months of log. Example: If it is a table in which data is only being added, something like an audit table, you just need the logs of August to see what was the first record added. 10 months is not atoo long a time to preserve tapes, specially in industries like financial services. Thats why i said - "assuming you can recall the tapes/files". But I do agree this solution may be for desparate situations.

Understood. I didn't mean to imply having to work back 10 whole months. Still - finding a specific point in time in a Transaction log is not much fun.

We have tapes from a LONG time ago as well. We just back up the T-logs separately, and rotate through them much faster. For "long term" storage, we validate 2 full backups/month for anything >3 months, and then store them (15 years). The T-log backups get cycled after 3 months.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 11

Thanks all for the suggestions. But, unfortunately we dont have backup tapes for August. And we are a development company and this is a development database, so I think this option wont work. I guess we will have to go with any other alternative on the application side.