Last day at work we were confronted with an error in our monthly production run. It turned out the Trustworthy property of the database had been set to “off”, so I suspected one of the dba’s to have performed a restore without knowing about this setting. It turned out our dba was helping us with disk space issues by moving database files around to new disks where there was more free space.
My first thought was: “They should have used the detach/attach method when they were moving the database files!”
I soon learned from another coworker that that was what they had probably done in the first place.
Somehow I thought that when you use the detach/attach method, the database settings are kept in the sys.databases table, and are applied automatically when you re-attach the database. I set out to test this, and booted one of my vms at my home workstation to do this.
I then detached and attached the database using the standard SSMS menu:
I queried sys.databases, and it turned out no information was left in this table about my database:
So, next time you want to move your database files. Do not use detach/attach.
Instead, alter the database to use the new file location. Then, take the database offline, move the files and set the database to online again. Presto!