SQLTeam.com | Weblogs | Forums

Change Tracking And Future Dated Commit_Time in sys.dm_tran_commit_table


#1

SQL Server 2012 Enterprise

I'm restoring a full database backup taken at Midnight CST, and running the following query:

select chg.ID, chg.Sys_Change_Version, chg.SYs_ChangeOperation, tct.commit_time
from changetable(changes tblPS_SCHEDULES_Details_CellInfo, 0) chg inner join 
     sys.dm_tran_commit_table tct on chg.Sys_Change_Version = tct.commit_ts

After converting Commit_Time from UCT to CDT, I'm getting times way past the midnight time when the full backup was taken. I"m actually getting current time stamps.

Can anyone help explain what is happening here?

I was hoping to use sys.dm_tran_commit_table to get the date/time that the change occurred per other forums, but when processing from a backup file, this doesn't give me realistic times.


#2

Past the END time of the backup?

Full Backup includes the Transaction Log, which is added at the end of the backup, so that a restore will restore to the end-time of the backup (or perhaps the start of the backup of the Log?!!)

I don't know if you can use STOP AT when restoring a Full Backup? I've only ever used it when restoring Log Backups onto a restore of a Full Backup