Published 23 March 2016
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.
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:
|Generate a deployment script||
|Execute a deployment script||
You can also revoke
DELETE permissions on the table to prevent the records from being modified.
If you need to deploy a migration script to the same database more than once, you can delete its records from
You need to remove both the Compare block and the Migration block from the table for the migration script you want to deploy again.
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:
- 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.
- Go to the Migrations tab.
- Expand Existing migration scripts and find the migration script you want to mark as deployed.
- Make a note of the migration script Name and Id
Edit the following
INSERTstatements, 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')
- Execute the above SQL on any databases you want to mark the migration script as deployed to.