Tuesday, August 5, 2014

Work: SQL Server - Allow Table Recreation

Solution

For SQL Server 2008:
Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation



Useful for development work, but worth re-enabling for production database to prevent someone from recreating a table especially for databases that hold data for reporting or contains a lot of data. Although recoverable, the server may be down for a long extended period of time. If recreation fails, the server will also be down until recovery is completed.


Error Message

Saving Changes is not permitted. The changes you have made require the following tables to be dropped and recreated. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.