SQL 2008 Management Tools: Can't save changes that require Recreation of Database

 

New stupid default setting in SQL Server's Management Tools: When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes. Instead you'll be greeted by this friendly dialog:

Notice that there's no option to save the changes - it's a hard rule that is applied upon saving and you can get past this other than back out of the dialog.

My first thought here is "Crap! Now what?" and off I go searching for an option to turn this off. Eventually I find a solution after a quick search online. As it turns out it's just an annoying configuration default setting that can be easily changed, but if you're like me and you spend a while searching around the Management Tools and finding nothing initially, I ended up eventually backing out of my initial database changes and losing a bit of work in the process. It wasn't until a bit later that I found the setting to change.

Hopefully you'll find this entry before you back out of database changes - you can get out of the above dialog, make the settings change and then still go ahead and save changes to your database.

The fix is: Go to Tools | Options | Designers | Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option:

and that does the trick.

This is a pretty harsh change IMHO. While I think it's a good idea that the tools now detect table recreation changes and can notify you, I think the better option by far would have been to pop up that initial dialog with a warning message AND provide an option on the buttons to either go forward or abort. Instead this arcane switch is going to cause some pause for most people familiar with the old tool behavior. It's not like this option is easy to find - I looked in the database options before I finally found it in the global tool options.

As it is, reverting back to the 'old' behavior now doesn't let you know that a table recreate is required either, so the behavior now is the same as was with the old tools. Here Microsoft added some useful functionality and then UI fails to expose it intelligently...

Source