To make sure the same migration script isn't deployed to the same database more than once, SQL Source Control records details of deployments that involve migrations.

When you deploy a migration script to a database, a RedGateLocal.DeploymentMetadata table is created on the target database to record the deployment. When you commit a new migration script, the script is marked as deployed on the database it was committed from.

The Name column lists the migrations already deployed to the database, identified by the name given to the migration script in SQL Source Control. Individual Compare blocks and Migration blocks are described on separate rows. To learn more about deployment blocks, see How migration scripts work.

For each migration, the table also records: the user who performed the deployment, when it took place, and the tool used.

Permissions

In some cases, you might want to restrict access to the deployment table. Depending on a user's deployment responsibilities, they require different permissions on the table:

You can also revoke UPDATE and DELETE permissions on the table to prevent the records from being modified.

Deleting records

If you need to deploy a migration script to the same database more than once, you can delete its records from RedGateLocal.DeploymentMetadata.

You need to remove both the Compare block and the Migration block from the table for the migration script you want to deploy again.

Inserting records

In some cases, you might want to prevent the deployment of a migration script to a database. To do this, you need to mark that migration script as deployed.

To do this:

1. In SQL Source Control, use the Object Explorer to select a database connected to the repository with the migration script you want to mark as deployed.
2. Go to the Migrations tab.
   
3. Expand Existing migration scripts and find the migration script you want to mark as deployed.
4. Make a note of the migration script Name and Id

5. Edit the following INSERT statements, replacing MigrationName and MigrationId with the values from the previous step:
   

   INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion]) 
   VALUES (N'MigrationName', 'Compare', 'Deployed', 'MigrationId auto', 'manual')
   INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion]) 
   VALUES (N'MigrationName', 'Migration', 'Deployed', 'MigrationId user', 'manual')

6. Execute the above SQL on any databases you want to mark the migration script as deployed to.