I have a SQL Server 2005 database, containing some tables which have a Timestamp (or RowVersion) column. I need to know the value of the current timestamp, so I use the following query: `SELECT CAST(@@dbts AS BIGINT);` This will return, for example, **10505**. Immediately afterwards, without updating, inserting, ... anything, I do a `BACKUP DATABASE` and a `RESTORE DATABASE` and I run the `SELECT` query again. Only this time, the result is **14000**, while none of the timestamps in the tables have increased. Why/how does this happen?
Martin Smith (imported from SE)
The value for `dbi_maxDbTimestamp` is stored on the database boot page. (page 9 in the primary data file). This is not written to every time a timestamp value is allocated. Instead SQL Server reserves a few thousand at a time. For example if `@@DBTS` is `2000` and the `dbi_maxDbTimestamp` is also `2000` then SQL Server updates the value written in the boot page to `6000` the next time it needs a timestamp value. The values from `2001 - 5999` are allocated in memory and "lost" if the database is set offline and then online again. The backup contains the copy of the boot page that has been updated to `6000`. So upon restoring it the timestamp values will start from this number. It knows nothing about any lost intermediate values. To see this CREATE DATABASE DbtsTest GO USE DbtsTest GO DBCC TRACEON(3604); CREATE TABLE T (X ROWVERSION) SELECT CAST(@@dbts AS BIGINT); DBCC PAGE(DbtsTest,1,9,1) On my system for a newly created database `@@dbts` is `2,000`. The `DBCC PAGE` output from above is ![DBCC Page 1] I have highlighted the timestamp value. `CAST(CAST(REVERSE(0xD007000000000000) AS BINARY(8)) AS BIGINT)` = `2000` INSERT INTO T DEFAULT VALUES SELECT CAST(@@dbts AS BIGINT); DBCC PAGE(DbtsTest,1,9,1) Now the `@@dbts` is reported as `2001` but looking at the page itself. ![DBCC Page 2] the timestamp value has changed. `CAST(CAST(REVERSE(0x7017000000000000) AS BINARY(8)) AS BIGINT)` = `6000`. Running DBCC DBTABLE('DbtsTest') at this point to view the [`DBTABLE` structure] shows both values dbt_maxDbTimestamp = 6000 dbt_dbTimestamp = 2001 Finally BACKUP DATABASE [DbtsTest] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\DbtsTest.bak' WITH NOFORMAT, NOINIT, NAME = N'DbtsTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 Then looking at the backup shows it is the 6,000 figure that is written. [![enter image description here]] Restoring the database and querying `SELECT CAST(@@DBTS AS BIGINT)` returns `6,000` as expected. : https://i.stack.imgur.com/tE1br.png : https://i.stack.imgur.com/xZozd.png : http://www.scheduler-usage.com/Uploads/philmalmaison/mdcache_whitepaper.pdf : https://i.stack.imgur.com/H4Uts.png : https://i.stack.imgur.com/H4Uts.png