or
Stijn imported from SE
sql-server
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?
Top Answer
Martin Smith
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][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][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][3] 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][4]][5]

Restoring the database and querying `SELECT CAST(@@DBTS AS BIGINT)` returns `6,000` as expected.


  [1]: https://i.stack.imgur.com/tE1br.png
  [2]: https://i.stack.imgur.com/xZozd.png
  [3]: http://www.scheduler-usage.com/Uploads/philmalmaison/mdcache_whitepaper.pdf
  [4]: https://i.stack.imgur.com/H4Uts.png
  [5]: https://i.stack.imgur.com/H4Uts.png
Why does @@dbts increase after backup/restore?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.