When you rename a table on your development database, SQL Source Control interprets this change as a DROP and CREATE. If someone else uses the Get latest tab to get these changes, data in the target table is lost. For more information about why this happens, see Working with migration scripts. 

To avoid this data loss, you can write a migration script to rename the table using the sp_rename stored procedure. This script replaces the DROP and CREATE statements the SQL Compare engine would otherwise generate for this change.

In this example, we'll use a migration script to rename a table in our development database.

1. Set up the databases

1. In SSMS, run this script on your server and refresh Object Explorer.

   SQL creation script

   USE master
   GO
   IF DB_ID('talkr_dev') IS NOT NULL
   BEGIN
        ALTER DATABASE talkr_dev SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE talkr_dev
   END
   GO
   IF DB_ID('talkr_prod') IS NOT NULL
   BEGIN
        ALTER DATABASE talkr_prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        DROP DATABASE talkr_prod
   END
   GO
    
   CREATE DATABASE talkr_dev
   GO
   USE talkr_dev
   GO
   CREATE TABLE [dbo].[users] (
           title varchar(10),
           name NCHAR(50),
           city varchar(25))
   GO
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
   INSERT INTO users ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
   GO
    
   CREATE DATABASE talkr_prod
   GO
   USE talkr_prod
   GO
   CREATE TABLE [dbo].[users] (
           title varchar(10),
           name NCHAR(50),
           city varchar(25))
   GO
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
   INSERT INTO users ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
   INSERT INTO users ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
   GO

   Two identical databases are created:

   • • talkr_dev (our development environment)
     • talkr_prod (our production environment)

   Both databases contain a table named people, populated with data.

   If these databases already exist on your server, executing this script will drop and create them again.

2. Link talkr_dev to an evaluation repository. To learn more, see Link to an evaluation repository.

   If you previously linked either of these databases to source control, you'll need to unlink them before continuing.

   
   
   

3. Now we need to perform an initial commit of all the objects in the talkr_dev database. With talkr_dev selected, go to the Commit tab.
   The users table is listed as a new change to commit:

   

4. Enter a comment and click Commit.
   The users table is committed to source control.

2. Rename the table

Now that our environments are set up, we'll rename the users table on our development environment (talkr_dev).

1. In Object Explorer, right-click talkr_dev and select New Query.

2. In the editor window, paste this SQL:

   EXEC sp_rename '[dbo].[users]', 'customers'
   GO
   CREATE SYNONYM dbo.users FOR customers
   GO

3. Execute the script. The server displays the following caution:
   
   We'll create a synonym for the table in our migration script to avoid these issues.

4. In Object Explorer, refresh talkr_dev to check that the table is renamed to customers:
   

3. Create a migration script

1. Open SQL Source Control.
2. Go to the Migrations tab.

3. Under Replace uncommitted schema changes, use the checkboxes to select the schema changes made in step 2.3.
   

   We're replacing uncommitted schema changes here because we've already renamed the table, but haven't yet committed it. Note that the rename is actually interpreted as a DROP and CREATE. The migration script will replace those changes in the deployment script.
4. Click Generate script
   SQL Source Control generates a migration script based on the uncommitted changes.
5. In the Name field, give the script a name (eg Rename users to customers).
6. In the editor window, delete the automatically generated DROP and CREATE statements.
    

7.  Paste this SQL into the migration script:

   EXEC sp_rename 'dbo.users', 'customers'
   GO
   CREATE SYNONYM dbo.users FOR customers
   GO

   Creating a synonym for the customers table here will avoid issues where the table is referenced using the old name.

8. Click Save and close.

This migration script now replaces the changes made in step 2.3.

4. Commit the changes

1. Go to the Commit tab.
2. Use the checkboxes to select the new migration script. The changes made in step 2.3 are automatically selected. 
3. Enter a comment and commit the changes.
   

5. Deploy the changes to production

In this example, we'll use SQL Compare to deploy a source control revision directly to our production database. It's possible to use Get latest in SQL Source Control to deploy these changes, however we don't recommend linking your production database directly to source control.

1. Open SQL Compare.
2. In the New Project window: 
   1. Under Source, select Source Control and then select Direct from source control.
      
      Point the Source repository at the evaluation repository created in step 1.2 (eg talkr_dev).
      
   2. Under Target, select Database:
      In the Database box, enter or select talkr_prod.
      
3. Click Compare Now. 
   SQL Compare highlights the differences between talkr_dev and talkr_prod.
4. Use the checkboxes to select:
   
   • the new customers table
   • the change to the users table.
     
5. Click Deployment Wizard.
6. On the Deployment method page, select Deploy using SQL Compare, then click Next.
7. On the Review Deployment Script page: 
   1. On the Deployment Script tab, check that the migration script written in step 3.5 is included in the deployment script:
      
   2. On the Summary tab, check that the migration script is listed under Actions:
      
8. Click Deploy Now and confirm the deployment.

SQL Compare deploys the changes to talkr_prod, then performs a recomparison. During deployment, the migration script is executed, using the sp_rename stored procedure in place of the DROP and CREATE statements generated by the SQL Compare engine. 

No changes are detected during the recomparison. This is because the two databases now have matching schema.

6. Verify changes to talkr_prod

Now that SQL Compare has successfully deployed the changes to production, check that the data is preserved: