When is the modify_date of an object updated? As part of the daily checks I'm setting up, I'm restoring the nightly full backups to our test server and checking the modified date of objects. This is not for any kind of precise change tracking or version control, more for proof that something has\hasn't been changed when somebody says something's different.

I've put a table on the test server and after initially populating it, a stored proc queries sys.objects for each restored database to get the modify_date for all the objects.  This date is compared to the modified date I first recorded. If the date in the table doesn't match that from the restored database then it flags. This produces the correct result in 99.9% of cases but a weird little problem has appeared. There are seven functions in our CRM database that show a modify_date of the date and time that the restore took place rather than a 'modified date'. 

I can't see any reason for this at all.  It only happens for a tiny fraction of the objects I'm testing across all our databases.  I can't find any triggers that could be updating the functions.  The databases are on SQL Server 2014 so CREATE OR ALTER isn't a possibility.  I've also checked the production servers and the modify_date for these objects matches the last time a restore was performed.

What could be altering, or appearing to alter, functions during a database restore?
What could be altering, or appearing to alter, functions during a database restore?


You can find it using default trace or use SQL Server DDL Triggers to Track All Database Changes. You can even use XEvents depending on your comfort level.

I would say, there has to be some process that does the alter. SQL Server just do not alter selective objects after a db restore.