I have been experiencing issues when making schema changes/stored proc definition changes. The changes that I make are not always persistent. I can make a change today then a day or two later I'll find out that the bug the change was fixing is still occuring. When I check the schema/stored proc my changes are nowhere to be found it will be just as it was before. Then I make the change again and usually it takes the second time around, but there have been a couple of instances where the second change has also reverted.

We're running SQL Server 2008 (10.0.6000.29)

I just wanted to find out if anyone else may have experienced this or might have a suggestion for a solution.

  • 3
    Either the changes aren't getting made in the first place - e.g. (1) You're applying them in a transaction and not committing it. (2) You're applying them somewhere else such as another instance of the database by mistake - or someone is overwriting them - e.g. restore backup or publishing old project version from SSDT that doesn't incorporate your changes. – Martin Smith May 6 '16 at 20:47
  • 3
    Also make sure you aren't making changes to a copy of your database. This can happen often when you are developing with Visual Studio and SQL Server Express, and your connection string enables the deprecated User Instance setting along with AttachDbFileName. See blogs.sqlsentry.com/aaronbertrand/bad-habits-attachdbfilename – Aaron Bertrand May 6 '16 at 22:26
  • We only run one database on our production server and I'm extremely cautious to make sure I know where I'm working when it comes time to make changes there. There are no other instances to confuse it with, no VS and no SQL Server Express. I only connect with SSMS 2014. I'll have to look into our backup process as its usually another DBA dealing with those. – agpoweredmg May 6 '16 at 22:53
  • Most schema changes get logged to the default trace BTW. And backup restores to msdb. – Martin Smith May 7 '16 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.