The rename is actually interpreted by SQL Compare as a DROP and CREATE and the migration script will replace those changes in the deployment script (the other option would be to start with a blank script, which you’d do if you’d already prepared your schema for the migration and committed those changes).īy clicking Generate script SQL Source Control generates a migration script based on the uncommitted changes and you can then replace the automatically generated DROP and CREATE statements. In this example you’re replacing uncommitted schema changes because you’ve already renamed the table, but haven’t yet committed it. On the Migrations tab and under Replace uncommitted schema changes, you’d then use the checkboxes to select the relevant schema changes. SQL Source Control will warn you that data loss may occur by committing the change and suggest you write a migration script. Once you’ve renamed your table (in this instance from users to customers) the next step would be to commit the changes to your source control system using the Commit changes tab in SQL Source Control. This script replaces the DROP and CREATE statements the SQL Compare engine would otherwise generate for this change. To avoid this data loss, you can write a migration script to rename the table using the sp_rename stored procedure. If another member of your team uses the Get latest tab to get these changes, data in the target table is lost. In the instance of renaming a table on your development database, SQL Source Control would interpret this change as a DROP and CREATE. How to avoid data loss when renaming a tableĬommon tasks which might be affected include splitting or merging columns and tables, adding a NOT NULL constraint to a column, changing the data type or size of a column, and renaming a table. You can learn more about what we’ve done, and why, in another recent blogpost of mine.īut here, I wanted to talk you through how it works using a common scenario. The latest release (shipped this week!) features an improved version of migration scripts, which allow you to write your own SQL to override the deployment script generated by SQL Compare, making it possible for your teammates to get the latest changes from source control without the risk of losing data. SQL Source Control plugs into SSMS and connects your databases to your version control system, allowing you to version control your database schemas, and then deploy them using Redgate’s trusted comparison engine SQL Compare. How to rename a table without data loss in SQL Server Management Studio using migration scripts - Simple Talk
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |