@@DBTS

Hi

There are tables in our database with timestamp column. As and when rows are updated/inserted in these tables, the timestamp column is getting populated which is getting reflected in the @.@.DBTS system variable.

However when we take the FULL database backup and restore the database, the @.@.BSTS is not showing the same timestamp value as it was when full DB backup was done (the behavior is unpredictable - once i did manage to get the correct @.@.DBTS value).

Does backup and restore process have any effects on the @.@.DBTS system variable.

Any guidance on parameter settings for Backup and restore will be appreciated to resolve this issue.

Cheers

Nishant Hate

I am assuming that is because Timestamp is a binary datatype used for row versioning, if you want the timeback you need to use datetime datatype. If you go to the Timestamp section of the link below Microsoft explains it in detail. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191240.aspx

|||

Hi

Thanks for your prompt response.

Let me give you background on the issue

There is a source system which is already developed from which i have to take incremental data every day and load into my warehouse.

There are no datetime fields in the source system for me to use and there is no scope for us to get the source system modified,

however each table is having the timestamp column which should give me daily new/updated records in the source system.

We can acheive this by storing the last loaded Timestamp in a control table and getting the latest timestamp using @.@.DBTS

Using this range we can get the incremental data from the source system.

This works fine.

However assuming that there is some issue with the source system and the database has to be restored from the backup.

Ideally i would have liked the @.@.DBTS to have the same value that was there when the backup was taken.

But the @.@.DBTS value is not coming the same infact it moves forward.

Logically @.@.DBTS moving forward should also be fine as I am interested in the records within the range and all the records will be included in the range.

So the question now is should I rely on @.@.DBTS even though it is different from the one which was there at the time of backup.

or am i better off to aviod this because of its unpredictable nature and build something to simulate the @.@.DBTS functionality i.e. Take max(timestamp) across all tables.

Please do let me know your views.

Cheers

Nishant hate