Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs
Contact   •   Articles   •   Products   •   Support   •   Search
Ad-free experience sponsored by:
ASPOSE - the market leader of .NET and Java APIs for file formats – natively work with DOCX, XLSX, PPT, PDF, images and more

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


Ah here's a silly new default 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:

PreventSave2

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:

PreventSavingChanges

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...

Posted in Sql Server  

The Voices of Reason


 

David Betz
August 16, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

I ran into that this morning myself. I was going to blog about it, but you beat me to it. Less work for me!

Peter Bromberg
August 17, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks for that. You're one step ahead of me and just saved me some time.

Pamela
August 18, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

I just downloaded and installed the Management Studio on Friday (ver 9.00.1399) and I don't have that checkbox in Tools/Options/Designers/Table and Database Designers.

pamela

Rick Strahl
August 18, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

@Pamela - because that the SQL 2005 tools. The above flag applies only to SQL 2008.

mark
August 19, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

learnt about that one in the beta. during that time i remember reading something about it being the default since there was still bugs in that code. turns out its now the default.

xun ding
August 21, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

wow, what are the improvement of SQL 2008 over SQL 2005? The fast upward march of versions made it really hard to decide whether to follow suite or stay put.

sue
September 08, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

thank you so much. stupid new "feature".
i agree with xun ding. hopefully that march will now stop for a while with 2008.
@xun ding: but now we have date AND time datatyp and tablenames as parameters.

Billy
October 15, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks for posting this, really helps.

Joe S
October 21, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Yes, thanks for the tip. I too did just about exactly as you describe in your article. I've also had the tool completely crash on me numerous times with the "object currently in use" error. I thought new versions were supposed to be improvements over the old. Hmmmmmmmmm

chris h
November 11, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

thanks for this. i recreated a table 15 times before i decided i wasnt doing anything wrong and there must be some setting i missed

Rick O'Shay
November 14, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

How does something like this get out of beta?

joan pons
November 17, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

who the hell at microsoft decide to put this stupid settings on ?
anyway, thanks rick!

Miljan
December 03, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks man, you saved mine precious time :)

Kiddin' THANK YOU SO MUCH!!!

BJ
December 15, 2008

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Some day they will decide that removing access to idiots is better than idiot-proofing everything. Sure it can be a scripted chagne but when you are just doing a quick and dirty table create and all at once you can't add another column just because you hit save before walking away from your desk for a bit. Whoa.

Ahmad
January 14, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

It worked like a charm, thanks!

Gg
February 05, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Yeah, I found this checkbox too.... BUT, what I'm more concerned about is that the reason they seem to have put that there is that it's a failsafe for loosing data if you're making a change to table that is live with data in it.

Is this telling me that we have taken a MASSIVE step backwards and can now not make changes to a table on the fly without loosing data!!??? Good god that's horrible... I make changes to indexes, fields etc. etc. all the time on live databases... because I live in the real world.

Ron Davis
February 09, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks. Ran across this and fixed it fast because of your post.

Axel VanHorn
February 17, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you,
this is one of those little devils that can drive you crazy,
lucky i was to found your post on my first search,

cheers

Mike
March 05, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks very much for this post, I was starting to get really annoyed about this.

jlangdon
March 14, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

That is totally moronic. Thanks, I was so taken aback I couldn't believe at first this would be a property setting, I figured I must be missing something. Thanks for posting.

Saman Perera
March 24, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Phew - this solved my problem - thank you very much.

phil w
March 26, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

+1 on that. Scary, but easy to turn off once you know. What were they thinking.

Michael Freidgeim
April 13, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Really silly and confusing default.
I've posted MS SQL feedback suggestion https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432179

Dave L
April 14, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

I agree that it can be frustrating to face this message.
I agree that it would be nice if this tool just did what you want ... perfectly.
I also think it would be nice if it presented a nicer dialog experience. Prompting you to permit it to change or move back.

But.
You should be aware that this tool was far from perfect in earlier versions. It did cause people to lose data. Especially bad on production systems.
This change was not bug, nor was the decision made without a lot of thought. But it was in response to a "Dammed if you do, dammed if you don't" conundrum. People who had lost data were asking "Why didn't MS protect me", but the same people had issues understanding that you can't change a nullable column to "NOT NULL" if it has data & there is no DEFAULT value.

No doubt you are aware that the changes that many people want to make are often far from trivial. Many can't be achieved by a simple ALTER statement. To do it correctly & to preserve data is often quite tricky. Some operations would require creating of TEMP tables to drop & recreate the objects in the new form. Perhaps needing a strict order of other ALTER statements to satisfy other pre-reqs (eg: FK constraints). Even when your change can be done with an ALTER stmts, it may require a table lock. Preventing access to your production system for an extended period of time while the DDL statement changed all rows in a huge table, checking the constraints & Foreign keys.

Yes, the SQL team know they need to invest more effort to improve this area.
A tool that does offer a safer "Production level" change experience is "Microsoft® Visual Studio Team System 2008 Database Edition" it can compare the delta between the original schema & your new schema & will emit scripts to make the changes you need. You can then review it & decide if that is something you'll run now or after hours.

Hope this helps you to understand there are risks in changing this option & hacking away on any system with data in it.
Dave

Rick Strahl
April 14, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

@Dave - agreed on making breaking changes to the database, but I think it would help if the tool was smart enough to figure that out. After all the scripts have to be built so the logic to figure that out have to be there.

And if the changes don't work it will fail which personally I think is a better behavior (works when it can, fails when it can) than just not doing it at all.

I think we agree better dialogs would help here. Ideally an option - go ahead do it anyway should be there.

Oliver
May 08, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanx for this info... saved me some additional digging around...

jeffanthonyfds
May 19, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you very much, I agree that this option could have been exposed in a more suitable way.

Abin Mathew
May 19, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanx for this info...

RunningCord
June 10, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks for the info. It is a great help. I need to echo Rick O'Shay. Surely during beta someone said na... I don't think so!

Giray
June 12, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

thanks sir for this update.
I was getting this error when adding a column (not sure why this requires recreation of the table)?

Darrin Thomas
June 12, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you. I was glad to find that it isn't just me that has these types of problems. Saved me some time and annoyance, thanks again.

Alex Levit
June 26, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks, I'm glad that somebody figured it already!

Pierrot
July 03, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

But why is the table re-creation necessary when I just add a column to a table ??? (of course, not a primary or foreign key column)

SNJ Walker
July 20, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks Rick. I'll add this to the collection of "dialogue-boxes-that-could-have-saved-some-hassle-with-a-smidgen-of-forethought" to use in mentoring graduates. Like many of the posts on WW, this will be useful for many people for quite some time.

siva
July 29, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

thanks for the quick help :)

Maria Smart
August 12, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

That helped. thanks

Andre De Jager
August 13, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks, you see the internet can be useful!!!!

Pieter van Kampen
August 25, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

It would be nice if MS protected me with this option from loosing data. But just moving columns around, or making a text column longer can be done without loosing data. So this option is over protective.
Can someone comment on what changes are protected by this option that DO cause you to loose data? (Apart from the obvious shrinking the size of a field.)

Thanks

Pieter

Joao Cardoso
August 28, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you very much. This problem was annoying me. Saved lot of time.

Omar
September 20, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you man, you have saved me houres

webHunter
September 29, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

wonderful
thanks

Jay
October 01, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

I always get excited when I'm searching for something and end up on your site because I know my search has ended!

imtiz
October 27, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks, you just saved my day (Y)

Craig
November 25, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Crap is a good word for it. Microsoft thinks everyone is an idiot.

rob
December 01, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks!!!!

Steve
December 09, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Awesome dude! This has saved me so much time. Thanks :-)

Anish
December 26, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanxs a lot ,I have worked a lot on to fix it .Stupid New feature Altest they can provide Some info about it .Once Again Thanxs a lotThanxs a lot!!!!!!!!!!!!!!!!

sam
December 26, 2009

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

You saved me a lot of frustation. Thank a lot for the tip. you rock!

Brian
January 05, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Saved me as well. Big thanks.

Mark
January 06, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Save me time, this was first search I clicked on. Thanks!

Joe
January 08, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks but what's benifit of this option?

Srichand
January 23, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanx much.It saved my time.

Spacemoses
January 29, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks for the tip!

zalam
February 04, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you. have been struggling with this since yesterday.

Mikhail M.
February 13, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you, I can work quicker now, as making changes to tables now become a matter of seconds.

Lana Goldenberg
March 02, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you for the tip.
It's really helpful, you saved me from jumping between SQL 2005 and SQL 2008 IDE. You rock!

David
March 07, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks man - this was starting to piss me off. I agree that this was something that could have been implemented better. First it could check if you even had any rows in the table before it bothered with any of the warnings. Next - the warning about data loss is cool, but they should give you the option to go ahead anyway.

:) David

mena
March 17, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you so much

Nate
March 19, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

I got this and deleted my table to make the changes. When I went to save the new table with the same name it said the table can not be created because it exists???
Searched sysobjects and it does not exist - But I can not create the table again.

Can anyone point me in the right direction?

Thanks!!

Pitclan
March 20, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Saved me too big time! Thanks a million!

tivolo
March 26, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks! When I saw the error I thought this was some kind of DB option that had been set that was blocking this. It would have been a while before I thought to look at the Management Studio options for this.

Tony
March 29, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks a lot, it solved my problem.

Peter Thurston
April 03, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Just adding my thanks to everyone elses. This was driving me mad. I can be sane again.

George
April 08, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks. It was driving me crazy

Oliver
April 21, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

This is the 2nd time around that I ran into this... man am I forgetful.... Thanx for your helpful post...

MatthewALL
April 29, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Good Post,

Turns a really annoying problem into a quick fix !

Now if Microsoft could have flagged that settings location in the error code !

What fun would that be......?

FC
April 29, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thank you very much for that. Luckily, I have stumbled on to your site a couple of times in the last few days while troubleshooting issues with C# and interfacing with SQL Server. This probably saved me a good couple of hours of work.

Sophie Hidalgo
May 12, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

OMG! i love you! THANK YOU! :)

Praise be to Rick!
May 21, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

all I can say is thanks for finding this out. the collective crack pipe of the usability guys on the SQL Server Team must be hot to the touch.

Tony
June 08, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks a bunch, what an annoyance!

Prabhat
July 13, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks, I ran into the same issue right now and on top of google results I found you. I totally agree with what you said. <i>"Here Microsoft added some useful functionality and then UI fails to expose it intelligently..."</i>

amjadk
August 01, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Thanks for the info. It saved lot of my time.
Regards,
Amjad Khan

jephroni
August 04, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Unbelievable...

rv
August 11, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

useful info for a 1st time user of SQL server 2008..

Peter
September 19, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

This was perfect advise. Easily understood and applied. Saved hours of headache and possible loss of data. Thank you.

Matt
September 27, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Rick, thanks for saving me lots of time.

JustJack
November 14, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Rick,

That's like the 20th time over the past several years that your blog has helped me out. Kudos and thanks amigo.

Jack

Nikolai
November 18, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

worlds worst feature ever.... thanks Rick

SM
November 20, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Really annoying. Thanks, saved me some frustration.

kas
December 10, 2010

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Wish I'd seen this weeks ago! I've been backing up, scripting, deleting, re-creating and restoring. Mugs game. Many thanks!

Mitch Bird
February 10, 2011

# re: Sql 2008 Management Tools: Can't save changes that require Recreation of Database

Beat Peter Bromberg to the mark ... Day-umm ... you are good. Thanks, Rick.
 

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2017