This page walks through an example case of writing and using a migration script.

When you rename a table in Management Studio, SQL Source Control interprets this as dropping and recreating the table. Any data in the table is lost. (For more information about why this happens, see What migration scripts do.) You can avoid this by creating a migration script to rename the table with the sp_rename stored procedure.

In this example, we'll rename a table in the development database (Dev) and create a migration script to avoid data loss. Then we'll commit the change to source control and finally apply it to the production database (Prod) using SQL Compare.

1: Set up the databases for this example

This script creates two databases, Dev and Prod. Both databases contain a table named Person with some data.

IF db_id('Dev') IS NULL
     CREATE DATABASE Dev
GO
      
IF db_id('Prod') IS NULL
     CREATE DATABASE Prod
GO
IF OBJECT_ID('Dev.[Person]', 'U') IS NULL
     BEGIN
          CREATE TABLE Dev.[dbo].[Person] (Name NCHAR(50))
          INSERT INTO Dev.[dbo].[Person] VALUES ('James')
          INSERT INTO Dev.[dbo].[Person] VALUES ('John')
          INSERT INTO Dev.[dbo].[Person] VALUES ('David')
     END
IF OBJECT_ID('Prod.[Person]', 'U') IS NULL
     BEGIN
          CREATE TABLE Prod.[dbo].[Person] (Name NCHAR(50))
          INSERT INTO Prod.[dbo].[Person] VALUES ('Mary')
          INSERT INTO Prod.[dbo].[Person] VALUES ('Margaret')
          INSERT INTO Prod.[dbo].[Person] VALUES ('Madeline')
     END

1. Run the script on your server and refresh the Object Explorer. 
   The Dev and Prod databases are created.

2. Link Dev and Prod to the same evaluation repository in source control.
    

   Make sure Dedicated database is selected as the development model.

   Now we need to do an initial commit of all the objects in the Dev database.

3. With Dev selected, go to the Commit changes tab.
   The Person table is listed as a new change to commit:

   
4. Click Commit.
   The Person table is committed to source control.

2: Create, test, and commit the migration script

We want to rename the table in Dev and apply the change to Prod.

If we wanted to do this conventionally, we might rename the table, write a migration script to prevent data loss, and commit the change and the migration script to source control. 

However, in this example, we'll write a migration script first, use the script to make the change, and then commit both the change and the script to source control. By using the migration script to make the change in the first place, we can test the script works.

1. Select Dev in the Object Explorer. In SQL Source Control, go to the Migrations tab and click Add migration script.
   

   The New migration script tab opens:

   

2. Paste this SQL into the main box in the migration script dialog:

   IF OBJECT_ID('[dbo].[Person]', 'U') IS NOT NULL
   BEGIN
   EXEC sp_rename '[dbo].[Person]', 'Customer'
   END

   This SQL is our migration script. It uses a guard clause to check for a table named Person. If the table exists, the script renames it Customer. If it doesn't exist, no changes are made. 

   For more information about guard clauses, and examples of other migration scripts, see Example V2 migration scripts.

3. Run the script.

   The server displays a caution:

   

   This is normal, and you can ignore it.

4. Refresh the Object Explorer.
   In Dev, the Person table has been renamed to Customer:
    
   This means the script worked correctly.

5. In the Migrations tab, in the Name field, type a name for the migration script and click Save and close.

6. Go to the Commit changes tab.
   New changes are listed:
   

   Although we've renamed the Person table to Customer, the change is listed incorrectly as a drop and create. This will be fixed in future versions.

   The RedGate schema contains the table-valued function MigrationHistory. This contains the new migration script so it can be used again in future deployments.

7. Click Commit.
   The changes are committed to source control.

3: Deploy the changes to Prod

Now we've committed the changes, we need to apply them to Prod using SQL Compare. 

sqlcompare /Database1:Dev /Database2:Prod /Synchronize

4: Verify the data is preserved

Now we've deployed the changes, we can verify that the data is preserved.

In Management Studio, refresh the Object Explorer and view the tables in Prod:

The Person table has been renamed to Customer.

If we select the top 1000 rows, we can see the data is preserved: