I'm having a strange issue in one SQL Server 2008 R2 I admin. Three days ago, a major renaming on the columns of a table was made due to a development. After the columns where renamed, the change was manually propagated to any DB dependencies and after that to the program making use of the server. Yesterday evening I was using the program and everything was working correctly. I even made inserts on the table using the new program interface.

Today, the boss was ranting because some important report (sp that depends on this table) was throwing an error. The report was changed to use the new column names, but when I got to the table, it was reverted to it's previous state (!!!) not only on one DB, but also on a 2nd one they have for another company (!!!!) The procedures still are correct (new development) and even the data I inserted after the change is in the table.

It never happened to me in other server. But I can remember just some months ago something similar happened. I'm really scared.

What would make a server revert table design changes in two different DBs after three days of normally working and several backups made?

  • I made those changes in MSSQL Management Studio. By designing the two tables and saving. – Sergio Jan 15 '15 at 16:11
  • Does the stored proc that generates the report get re-created as part of some daily batch? – LowlyDBA Jan 15 '15 at 16:26
  • 3
    Only someone manually un-doing the change, rebuilding the table based on an older script, or restoring a backup to a point before the change. Also, you should use ALTER TABLE scripts and not point-and-click in the GUI. For one thing, you can put your ALTER TABLE scripts in source control and track exactly what was done. – Aaron Bertrand Jan 15 '15 at 16:34
  • 1
    You could try to look in the default trace to see if and when an object was altered. See here for more info: simple-talk.com/sql/performance/… – spaghettidba Jan 15 '15 at 17:26
  • @John M: No the sps are static. – Sergio Jan 16 '15 at 18:08

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.