The changes you have made require the following tables to be dropped and re-created

When changing tables in SQL Server Management Studio 2008 you may get the following error: Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. I was surprised when I saw this message first but there is very simple solution.

From top menu select Tools and then Options. Select Designer and Table and Database Designers.

Allow changes that require table re-creation

Uncheck the box Prevent saving changes that require table re-creation. Now you can edit your tables without being stopped by re-creation limits.

Update. As mxmissile pointed out in his comment then don’t use this on tables with millions of rows.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    28 thoughts on “The changes you have made require the following tables to be dropped and re-created

    • April 8, 2009 at 5:01 pm
      Permalink

      Don’t try this on a table that contains millions of records or a live database.

    • June 9, 2009 at 5:56 pm
      Permalink

      Thanks for the info. Certainly this should be only done during development phase in an empty table.

    • July 17, 2009 at 12:16 pm
      Permalink

      Thank you very much.

    • April 17, 2010 at 2:43 pm
      Permalink

      Thanks, I’ve been facing this problem quite a lot

    • June 30, 2010 at 10:57 am
      Permalink

      Thank you. I was just about to drop all my DB tables and try to create the whole DB from scratch ….saved my life!

    • September 1, 2010 at 9:54 am
      Permalink

      Thank you very much.Solved my problem.

    • September 5, 2010 at 9:41 pm
      Permalink

      You saved my from blowing a deadline on an active directory project with 25,000 users! Thank you so much for taking a moment to share.

    • September 13, 2010 at 9:16 am
      Permalink

      I’ve read similar posts on a dozen different websites, but none are addressing the most important concern.

      I need to add a column to a table contain a few million rows. After testing this solution on a test database, I find that it only takes about 30 seconds to complete; not a big deal.

      So is this safe to do on a production table after hours?

      Is there any risk of losing data of any kind (table data, constraints, stats, indexes)?

      I guess I don’t understand why MS locks you out in the first place. Is it solely because of the potential of hanging a production database for a long period of time (because its such an extensive operation)?

      Thanks for any insight!

    • September 14, 2010 at 9:18 am
      Permalink

      Thanks for question, Andy. There is reason why MS locks this feature by default. Re-creating large tables may take more time than couple of seconds, it may take even hours to make change like this on large tables. The way how Management Studio performs this operation is very costy.

    • September 20, 2010 at 4:56 pm
      Permalink

      So it has nothing to do with data loss, purely a consideration because of the intensity of the operation?

    • September 20, 2010 at 8:42 pm
      Permalink

      This far I have done this operation without data losses. If tables are large then it may take long hours to recreate them and fill with data again.

    • September 24, 2010 at 12:30 am
      Permalink

      thanks for the information, that is very helpful one.. cheers!!!

    • December 15, 2010 at 11:58 am
      Permalink

      Thanks a lot. Saved me my day. I underwent the pain of dropping and re-creating the entire DB (development) a week earlier. Whew!!! An intimation along the “Save Cancelled” dialog box would have been a better option. Seems Microsoft had left its SQL users running helter-skelter by hiding such control info. Also makes an impression that Microsoft’s curve bends low towards user-friendliness and goes soaring towards sophistication. While it seems good, not to forget the huge base of confused users left behind.

    • January 22, 2011 at 11:37 am
      Permalink

      Thank you very much. I was facing this problem since a long time. Your blog post solved it.

    • February 18, 2011 at 7:26 pm
      Permalink

      thanks! i thought it was a server setting!

    • September 2, 2011 at 3:29 am
      Permalink

      good info… thanx

    • November 2, 2011 at 7:20 pm
      Permalink

      Thank you so much!!!saved my time..:)

    • January 11, 2012 at 12:28 pm
      Permalink

      Thanks… hate MS so much.. forced to work with this terrible DBMS for my current project.

    • July 1, 2013 at 2:25 pm
      Permalink

      Very Very Thank you my friend!!!!!!!!!

    • April 6, 2025 at 6:34 pm
      Permalink

      My brother recommended I might like this website.
      He was once entirely right. This post truly made my day.
      You can not imagine just how so much time I had spent for this
      info! Thanks!

    • April 16, 2025 at 12:40 am
      Permalink

      I think what you published made a ton of sense. But, think
      about this, suppose you were to write a awesome headline?
      I ain’t saying your content is not good., however what if you added a post title that makes people want more?
      I mean The changes you have made require the following tables
      to be dropped and re-created is kinda vanilla. You might peek
      at Yahoo’s home page and watch how they create post
      headlines to get people to click. You might try adding a video
      or a related picture or two to get people interested about
      what you’ve got to say. In my opinion, it would make your posts a little livelier.

    • April 20, 2025 at 7:08 am
      Permalink

      With havin so much written content do you ever run into any issues of
      plagorism or copyright infringement? My website has a
      lot of completely unique content I’ve either created myself or outsourced but it appears a lot of
      it is popping it up all over the web without my authorization. Do you know any methods to help stop content from being ripped off?
      I’d certainly appreciate it.

    • May 30, 2025 at 8:32 am
      Permalink

      I like the helpful info you provide in your articles.
      I’ll bookmark your blog and check again here regularly. I am quite certain I’ll
      learn many new stuff right here! Best of luck for the next!

    • June 10, 2025 at 5:15 am
      Permalink

      For latest news you have to pay a quick visit the web
      and on world-wide-web I found this website as a most excellent web site for hottest updates.

    • June 26, 2025 at 12:16 pm
      Permalink

      I am truly delighted to glance at this webpage posts which consists of plenty of valuable facts, thanks for providing
      these statistics.

    Leave a Reply

    Your email address will not be published. Required fields are marked *