Please see the below snapshot, showing the timestamps of all the data files as well as transaction log files for one of the SQL Server instances under my management. This is a production instance.
Today is Dec 27th, 2019. However, as you can see, all the timestamps are showing much older times.
The truth is, I took this snapshot a few days ago on 24th Dec. However, as I checked today, the situation is still unchanged.
The snapshot above was taken from the user databases. I checked the system databases as well, which are residing on a separate drive, and took the snapshot of the files' timestamps, which were as shown below:
These databases are certainly not super active. But, I am certain that there are transactions running all the time. Additionally, I looked at the "Schema Changes History" report for a few of them, and could confirm that our maintenance jobs have been working and rebuilding indexes in as recently as yesterday 26th Dec.
The databases are all in FULL recovery model (although that may not be relevant – just a fact to mention). The OS is Windows NT 6.0 (= Windows Server 2007) and SQL Server version is 2008 SP3. Both the OS and SQL Serve are due for upgrade next year Q1. Also as is evident from the above snaps, with data files and log files both residing in the same disk drive and folder, the configuration of this server which I have "inherited" is certainly not the best. So, hopefully all will be fixed during the planned upgrade/migration activity which is going to happen in a few months.
Now the question for me is why the timestamps of these files are not getting updated, despite the databases being active anyway? (even manually running of Checkpoint didn't change anything). and then, if the transaction log files are not getting updated, will the backups be good and reliable? Do they really contain the records that they are supposed to?
Best Answer
In answer to your main question ("what is being backed up"), everything that is supposed to be ? More seriously, you can't use those time stamps as a sign of changes happening in the database files. SQL Server doesn't make any guarantees about how the modified date of database files will be changed or updated.
There is an interesting blog post here that attempts to find patterns with when and how the date modified stamp is updated on MDF files:
SQL Server Database File - Date Modified
Their observations line up with your situation I think: normal writes to the data file don't update the date modified stamp. It looks like growth events (manual or automatic) would update that value, as would restarting the service (or anything else that might "open" or "close" a database - using the autoclose feature, explicitly taking the DB offline, etc).
I expect 19/12/2019 9:52 PM corresponds with a Windows or SQL Server restart, which is how several of the date / times got sync'd up.
However, again, this behavior is undocumented. Any patterns we notice could change at any time, or have unexpected edge cases.
For SQL Server data files residing on an NTFS file system, this applies:
Though, to be clear, that's a guarantee provided by Windows. We can't necessarily know a ton about how SQL Server creates and destroys file handles in every scenario.
David Browne (a Top Microsoft Man) mentioned this in a comment, which is useful to know: