Creation date of a row

Question

Post reply

Creation date of a row

prady_1988

Say Hey Kid

Points: 680
More actions
March 31, 2011 at 6:38 am

#237741

Hi,
The problem is, the database is growing everyday and now the option right now is to archive all records before Jan 1st 2009 into a new DB. But in none of the tables the creation/modified dates are stored. How can i get these record creation and modified dates?
Thanks in advance 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

without a field holding this information, you can't get it sorry.

prady_1988 Say Hey Kid Points: 680 More actions · Answer 2

Thanks for the fast reply

always thought there would be some built-in timestamps for records inserted. is there any workaround for this?

steveb. SSC-Forever Points: 46733 More actions · Answer 3

not really.

you could restore a backup from prior to 1st Jan 2009 (if you have one) to another db and use this as the basis for comparisons to find out earlier records.

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

You could add a datetime column with a default of getdate for future use. That would show you when it was added, but not when it was modified. You'd need to modify your applications for that.

prady_1988 Say Hey Kid Points: 680 More actions · Answer 5

Yes i could restore a backup with DB prior to Jan 1st 2009, but i would need another DB containing only the records which were added after Jan 1st 2009. The other records prior to that, i need to put them in another archive DB. Is there any way to separate them?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

prady_1988 (4/1/2011)
Yes i could restore a backup with DB prior to Jan 1st 2009, but i would need another DB containing only the records which were added after Jan 1st 2009. The other records prior to that, i need to put them in another archive DB. Is there any way to separate them?

Surely if you have a set of all rows from before that date (the old backup) and the set of all rows, it's a trivial query to get the one that are only in the one table. Exists, In or Except will do the job.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prady_1988 Say Hey Kid Points: 680 More actions · Answer 7

GilaMonster (4/1/2011)
prady_1988 (4/1/2011)
Yes i could restore a backup with DB prior to Jan 1st 2009, but i would need another DB containing only the records which were added after Jan 1st 2009. The other records prior to that, i need to put them in another archive DB. Is there any way to separate them?
Surely if you have a set of all rows from before that date (the old backup) and the set of all rows, it's a trivial query to get the one that are only in the one table. Exists, In or Except will do the job.

So do i need to check if each and every primary key exists and then insert it?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If by 'each and every' you mean a cursor or a row-by-row comparison, no.

Look up EXISTS (and IN) and it should become clear how they work. If you need further help, post the table definition.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 9

to help you visualize, here's a prototype to review and get your head around it.

assume you added a CreatedDate column today to a specific table.

you restored a backup as suggested to a database named 'History'

now you can compare old data vs new data.

you could update with an IN or EXISTS as suggested:

UPDATE PRODUCTION.DBO.INVOICES

SET CREATEDDATE = '01/01/2009'

WHERE INVOICEID IN

(

--this may select a million records...

SELECT INVOICEID

FROM HISTORY.DBO.INVOICES

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!