January 18, 2019 at 1:56 am
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 I've got a stored proc that compares the modified date for each object in a database with the modified date I first recorded. If the date in the table doesn't match the backup then it flags. This works in 99.9% of cases but a weird little problem has appeared. There are seven functions in our CRM database that show the date and time that the restore took place rather than the modified date. I can't see any reason for this and it only happens for a tiny fraction of the objects I'm testing.
Does anybody have any suggestions as to why this happens?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 21, 2019 at 3:06 am
I've established why the modify_date column was changing although I've not tracked down precisely *what* was making the change. It was both obvious when I thought about it and obscure in details. I looked at the default trace and I found series of system database triggers related to replication that were deleted in the approximate time frame of the modifications. Although they may not have been directly involved, they pointed me in the right direction.
The database on the live server is replicated and when restoring a replicated database the replication settings are not preserved. The seven functions in question were marked 'is_schema_published' on the live server. When the database was restored, the is_schema_published column was set to 0. This change meant that the modify_date was changed to approximately match the date and time of the restore. Every other replicated object is marked as is_published on the live but not on the restored version. For some reason the change to the is_published column does not seem to affect the modify_date when it's done as part of the restore.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply