Example - deploying with migration scripts
Published 25 March 2013
This example shows you how to create a migration script for a database so you can deploy automatically without fear of data loss.
The example uses:
- SQL Source Control 3
- SQL Compare Pro 10
- The Subversion source control system
- The Tortoise SVN client for Subversion
In the example, the Magic Widget Company has a SQL Server database running on a live web server. This database contains a number of tables, views, stored procedures, and other database objects.
The Magic Widget Company's development is working on an update to this database. They've already created a copy of the production database as a baseline to develop against. They've linked their development copy of the database to source control, and will now set up the ability to create and manage migration scripts.
The example has four stages:
- Set up the databases
- Specify a migration scripts location
- Create a migration script
- Deploy the migration script
1. Set up the databases
This example uses the WidgetDev and WidgetStaging databases.
To create the databases on your SQL Server:
View the SQL creation script for the databases (click to expand)
SQL creation script
/* Create Widget Staging */ USE tempdb GO IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WidgetStaging' ) DROP DATABASE WidgetStaging GO CREATE DATABASE WidgetStaging GO USE WidgetStaging GO CREATE TABLE [dbo].[WidgetPrices] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [WidgetID] [int] NULL , [Price] [money] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WidgetDescriptions] ( [WidgetID] [int] IDENTITY (1, 1) NOT NULL , [Description] [varchar] (500) NULL , [WidgetName] [varchar] (50) NULL )ON [PRIMARY] GO CREATE TABLE [dbo].[Widgets] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [Description] [varchar] (50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WidgetReferences] ( [WidgetID] [int] IDENTITY NOT NULL , [Reference] [varchar] (50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[WidgetReferences] WITH NOCHECK ADD CONSTRAINT [PK_WidgetReferences] PRIMARY KEY NONCLUSTERED ( [WidgetID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[WidgetPrices] WITH NOCHECK ADD CONSTRAINT [PK_WidgetPrices] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Widgets] WITH NOCHECK ADD CONSTRAINT [PK_Widgets] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE VIEW dbo.CurrentPrices AS SELECT WidgetID, Price, Description FROM Widgets INNER JOIN WidgetPrices ON Widgets.RecordID = WidgetPrices.WidgetID GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO /* Populate WidgetStaging with data */ USE WidgetStaging GO SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON GO SET DATEFORMAT YMD GO SET XACT_ABORT ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION -- Pointer used for text / image updates. This might not be needed, but is declared here just in case DECLARE @pv binary(16) -- Add 10 rows to [dbo].[WidgetPrices] SET IDENTITY_INSERT [dbo].[WidgetPrices] ON INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (1, 9, 698.1374) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (2, 6, 325.4914) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (3, 6, 693.4032) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (4, 5, 116.1689) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (5, 3, 751.7997) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (6, 5, 49.3884) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (7, 5, 422.2571) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (8, 1, 895.2037) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (9, 10, 596.7856) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (10, 4, 213.4546) SET IDENTITY_INSERT [dbo].[WidgetPrices] OFF -- Add 10 rows to [dbo].[WidgetReferences] SET IDENTITY_INSERT [dbo].[WidgetReferences] ON INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (1, 'HRGM1S45G9L67Z6M9V74RCKV0ZQCYOW01OXJMLTMGB0') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (2, '0ULCDFPYJID56LL11R7RDK5J1MZN1KNFBGV6EDYIYYHJA') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (3, 'D10RLP49QF') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (4, '1AQF2WZUXTPQENN') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (5, 'OTE3L899YN') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (6, 'YYB2QGHC283V2IODYNAL3XWFFCB3S1GGFL0V') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (7, 'RZAWBKKLYCLXVAMN1612') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (8, 'NE4EJ') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (9, 'RMGGHTR7N0ORCCUHZQ6XQUSDFZTP4L5ISJTYHW3443YNCEOQ1') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (10, 'ED8LAXU20IZ122V6ZTIVZ3M1SMV500B3NY6R968W4E') SET IDENTITY_INSERT [dbo].[WidgetReferences] OFF -- Add 10 rows to [dbo].[Widgets] SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (1, 'quad trepicandor rarendum quo non Pro quis') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (2, 'non linguens cognitio, imaginator estis') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (3, 'estum. travissimantor fecit. homo, et') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (4, 'non transit. venit. nomen quad esset pladior Sed') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (5, 'esset quantare Versus et quantare Sed novum Multum') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (6, 'trepicandor ut egreddior trepicandor apparens') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (7, 'transit. Multum Sed esset venit. sed pladior quad') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (8, 'quad habitatio estis quoque Sed et et rarendum') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (9, 'in vantis. Longam, linguens novum Tam quartu bono') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (10, 'quis fecit, Longam, linguens Sed gravum funem.') SET IDENTITY_INSERT [dbo].[Widgets] OFF -- Add 10 rows to [dbo].[WidgetDescriptions] SET IDENTITY_INSERT [dbo].[WidgetDescriptions] ON INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (1, 'vantis. fecundio, quad eggredior. nomen nomen quad dolorum gravum ut et quantare vobis quartu bono quad funem.', '89541') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (2, 'apparens Id novum Sed estis si gravum apparens gravum dolorum fecundio, quis et glavans cognitio, quoque', '19614') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (3, 'Et Pro plorum trepicandor pladior e fecundio, vobis novum bono pars Quad regit, travissimantor e cognitio, nomen', '21711') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (4, 'volcans Longam, estis non non estis et Id vantis. esset transit. Sed et fecit, vobis fecit. plurissimum quorum rarendum trepicandor quantare cognitio, si', '51534') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (5, 'essit. volcans quad novum in brevens, si manifestum cognitio, non eudis glavans e delerium. eggredior.', '40493') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (6, 'glavans eggredior. eudis delerium. cognitio, pars fecit. funem. essit. si pladior eggredior. glavans', '78782') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (7, 'quo, plorum quo vobis manifestum Et imaginator eggredior. rarendum et quad fecit. linguens delerium. linguens', '50517') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (8, 'Longam, quorum glavans ut Longam, e e venit. brevens, parte dolorum Longam, Quad et esset novum Sed Tam', NULL) INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (9, 'bono Sed plorum quad quad si plurissimum et Quad gravis homo, bono sed quo egreddior imaginator plorum Sed', '38345') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (10, 'pladior cognitio, quartu volcans vobis pladior nomen Id transit. quorum plurissimum sed vantis. in quis', '86125') SET IDENTITY_INSERT [dbo].[WidgetDescriptions] OFF COMMIT TRANSACTION GO /* Create Widget Dev */ IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WidgetDev' ) DROP DATABASE WidgetDev GO CREATE DATABASE WidgetDev GO USE WidgetDev GO CREATE TABLE [dbo].[WidgetPrices] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [WidgetID] [int] NULL , [Price] [money] NULL , [DateValidFrom] [datetime] NULL , [DateValidTo] [datetime] NULL , [Active] [char] (1) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WidgetDescriptions] ( [WidgetID] [int] IDENTITY (1, 1) NOT NULL , [Description] [varchar] (500) NULL , [WidgetName] [varchar] (50) NULL )ON [PRIMARY] GO CREATE TABLE [dbo].[Widgets] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [Description] [varchar] (50) NULL , [SKU] [varchar] (20) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[WidgetReferences] ( [WidgetID] [int] NOT NULL , [Reference] [varchar] (50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[WidgetReferences] WITH NOCHECK ADD CONSTRAINT [PK_WidgetReferences] PRIMARY KEY NONCLUSTERED ( [WidgetID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[WidgetPrices] WITH NOCHECK ADD CONSTRAINT [DF_WidgetPrices_DateValidFrom] DEFAULT (getdate()) FOR [DateValidFrom], CONSTRAINT [DF_WidgetPrices_Active] DEFAULT ('N') FOR [Active], CONSTRAINT [PK_WidgetPrices] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Widgets] WITH NOCHECK ADD CONSTRAINT [PK_Widgets] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO CREATE INDEX [IX_WidgetPrices] ON [dbo].[WidgetPrices]([WidgetID]) ON [PRIMARY] GO CREATE INDEX [IX_WidgetPrices_1] ON [dbo].[WidgetPrices]([DateValidFrom]) ON [PRIMARY] GO CREATE INDEX [IX_WidgetPrices_2] ON [dbo].[WidgetPrices]([DateValidTo]) ON [PRIMARY] GO GRANT SELECT ON [dbo].[WidgetPrices] TO [public] GO DENY REFERENCES , INSERT , DELETE , UPDATE ON [dbo].[WidgetPrices] TO [public] CASCADE GO GRANT SELECT ON [dbo].[Widgets] TO [public] GO DENY REFERENCES , INSERT , DELETE , UPDATE ON [dbo].[Widgets] TO [public] CASCADE GO ALTER TABLE [dbo].[WidgetPrices] ADD CONSTRAINT [FK_WidgetPrices_Widgets] FOREIGN KEY ( [WidgetID] ) REFERENCES [dbo].[Widgets] ( [RecordID] ) GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE VIEW dbo.CurrentPrices AS SELECT WidgetPrices.WidgetID, WidgetPrices.Price, Widgets.Description FROM dbo.Widgets INNER JOIN dbo.WidgetPrices ON dbo.Widgets.RecordID = dbo.WidgetPrices.WidgetID WHERE dbo.WidgetPrices.Active = 'Y' GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT SELECT ON [dbo].[CurrentPrices] TO [public] GO DENY INSERT , DELETE , UPDATE ON [dbo].[CurrentPrices] TO [public] CASCADE GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE prcActivatePrices AS UPDATE WidgetPrices SET Active='N' WHERE GetDate()<DateValidTo OR GetDate()>DateValidFrom UPDATE WidgetPrices SET Active='Y' WHERE GetDate()>=DateValidFrom OR GetDate()<=DateValidFrom GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO DENY EXECUTE ON [dbo].[prcActivatePrices] TO [public] CASCADE GO /* Populate WidgetDev with data */ USE WidgetDev GO SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON GO SET DATEFORMAT YMD GO SET XACT_ABORT ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION -- Pointer used for text / image updates. This might not be needed, but is declared here just in case DECLARE @pv binary(16) -- Drop constraints from [dbo].[WidgetPrices] ALTER TABLE [dbo].[WidgetPrices] DROP CONSTRAINT [FK_WidgetPrices_Widgets] -- Add 10 rows to [dbo].[WidgetDescriptions] SET IDENTITY_INSERT [dbo].[WidgetDescriptions] ON INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (1, 'vantis. fecundio, quad eggredior. nomen nomen quad dolorum gravum ut et quantare vobis quartu bono quad funem.', '89541') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (2, 'apparens Id novum Sed estis si gravum apparens gravum dolorum fecundio, quis et glavans cognitio, quoque', '19614') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (3, 'Et Pro plorum trepicandor pladior e fecundio, vobis novum bono pars Quad regit, travissimantor e cognitio, nomen', '21711') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (4, 'volcans Longam, estis non non estis et Id vantis. esset transit. Sed et fecit, vobis fecit. plurissimum quorum rarendum trepicandor quantare cognitio, si', '51534') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (5, 'essit. volcans quad novum in brevens, si manifestum cognitio, non eudis glavans e delerium. eggredior.', '40493') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (6, 'glavans eggredior. eudis delerium. cognitio, pars fecit. funem. essit. si pladior eggredior. glavans', '78782') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (7, 'quo, plorum quo vobis manifestum Et imaginator eggredior. rarendum et quad fecit. linguens delerium. linguens', '50517') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (8, 'Longam, quorum glavans ut Longam, e e venit. brevens, parte dolorum Longam, Quad et esset novum Sed Tam', NULL) INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (9, 'bono Sed plorum quad quad si plurissimum et Quad gravis homo, bono sed quo egreddior imaginator plorum Sed', '38345') INSERT INTO [dbo].[WidgetDescriptions] ([WidgetID], [Description], [WidgetName]) VALUES (10, 'pladior cognitio, quartu volcans vobis pladior nomen Id transit. quorum plurissimum sed vantis. in quis', '86125') SET IDENTITY_INSERT [dbo].[WidgetDescriptions] OFF -- Add 10 rows to [dbo].[WidgetReferences] INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (1, 'HRGM1S45G9L67Z6M9V74RCKV0ZQCYOW01OXJMLTMGB0') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (2, '0ULCDFPYJID56LL11R7RDK5J1MZN1KNFBGV6EDYIYYHJA') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (3, 'D10RLP49QF') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (4, '1AQF2WZUXTPQENN') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (5, 'OTE3L899YN') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (6, 'YYB2QGHC283V2IODYNAL3XWFFCB3S1GGFL0V') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (7, 'RZAWBKKLYCLXVAMN1612') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (8, 'NE4EJ') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (9, 'RMGGHTR7N0ORCCUHZQ6XQUSDFZTP4L5ISJTYHW3443YNCEOQ1') INSERT INTO [dbo].[WidgetReferences] ([WidgetID], [Reference]) VALUES (10, 'ED8LAXU20IZ122V6ZTIVZ3M1SMV500B3NY6R968W4E') -- Add 10 rows to [dbo].[Widgets] SET IDENTITY_INSERT [dbo].[Widgets] ON INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (1, 'quad trepicandor rarendum quo non Pro quis') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (2, 'non linguens cognitio, imaginator estis') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (3, 'estum. travissimantor fecit. homo, et') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (4, 'non transit. venit. nomen quad esset pladior Sed') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (5, 'esset quantare Versus et quantare Sed novum Multum') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (6, 'trepicandor ut egreddior trepicandor apparens') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (7, 'transit. Multum Sed esset venit. sed pladior quad') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (8, 'quad habitatio estis quoque Sed et et rarendum') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (9, 'in vantis. Longam, linguens novum Tam quartu bono') INSERT INTO [dbo].[Widgets] ([RecordID], [Description]) VALUES (10, 'quis fecit, Longam, linguens Sed gravum funem.') SET IDENTITY_INSERT [dbo].[Widgets] OFF -- Add 10 rows to [dbo].[WidgetPrices] SET IDENTITY_INSERT [dbo].[WidgetPrices] ON INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (1, 9, 698.1374) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (2, 6, 325.4914) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (3, 6, 693.4032) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (4, 5, 116.1689) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (5, 3, 751.7997) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (6, 5, 49.3884) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (7, 5, 422.2571) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (8, 1, 895.2037) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (9, 10, 596.7856) INSERT INTO [dbo].[WidgetPrices] ([RecordID], [WidgetID], [Price]) VALUES (10, 4, 213.4546) SET IDENTITY_INSERT [dbo].[WidgetPrices] OFF -- Add constraints to [dbo].[WidgetPrices] ALTER TABLE [dbo].[WidgetPrices] ADD CONSTRAINT [FK_WidgetPrices_Widgets] FOREIGN KEY ([WidgetID]) REFERENCES [dbo].[Widgets] ([RecordID]) COMMIT TRANSACTION GO
- Copy the script, paste it into a query window in SQL Server Management Studio, and run it.
The databases and their schema are created.
2. Specify a migration scripts location
This example assumes WidgetDev is already linked to source control, but no migration scripts folder has been set up.
Linking associates the database with a location in source control. For detailed instruction on linking a database to source control, see Linking to source control.
Migration scripts are customizable change scripts that SQL Compare uses in deployment. They can be used to avoid data loss and to avoid the need to repeatedly make manual configuration changes.
To specify a location to store migration scripts:
- Make sure WidgetDev is selected in the Object Explorer.
- In SQL Source Control, on the Setup tab, under Source control details, click Set up migration scripts:
The Link Migrations Repository dialog box opens:
No migration scripts folder is set up, so we'll create one.
- Next to the migration scripts repository URL, click Browse.
The Select Migration Scripts Repository dialog box is displayed:
The best place to store your migration scripts depends on your development environment. In this example, we will create a sibling folder at the same level as the database schema.
For more information, see Working with Migrations.
- Click Create Folder.
The Create Folder dialog box is displayed.
- In Folder Name, type a name for the folder.
In this example, call the folder WidgetMigrations
- Click Create.
The new folder is displayed on the Select Migration Scripts Repository dialog box.
- Make sure the Widget Migrations folder is selected, and click Select.
The new migration scripts location is selected
- On the Link Migrations Repository dialog box, click Link.
Migration scripts are now set up.
3. Create a migration script
In this example, we want to add a NOT NULL constraint to a column in the WidgetDescriptions table in WidgetDev.
In SQL Server Management Studio, open a new query window, and run the following SQL queries:
UPDATE [WidgetDescriptions] SET WidgetName = '<Unset>' WHERE WidgetName IS NULL
This updates all rows in the table with the value <Unset> for the WidgetName column.
ALTER TABLE [WidgetDescriptions] ADD CONSTRAINT CK_WidgetName_NOTNULL CHECK (WidgetName IS NOT NULL)
This adds a NOT NULL constraint on the WidgetName column.
If we commit this change and then try to deploy it to WidgetStaging using SQL Compare, the deployment will fail; a default value is required when adding a NOT NULL constraint to a column with existing data.
We can specify a default value if we commit the change as a migration script:
- In SQL Source Control, on the Commit Changes tab, right-click the change to WidgetDescriptions, and click Add New Migration Script:
The Create migration script dialog box is displayed:
- Make sure the table WidgetDescriptions is selected, and click Create and edit script.
A new query window opens, populated with the script to deploy the change:Here you can specify a name for the script and edit it to prevent errors in deployment.
Before the final
ALTER TABLE
statement in the script, add the following SQL:UPDATE [WidgetDescriptions] SET WidgetName = '<Unset>' WHERE WidgetName IS NULL GO
- Click Proceed to commit.
SQL Source Control displays a confirmation dialog box: - Click Save.
The migration script is now listed on the Commit Changes tab: - Type a commit comment, and click Commit.
The migration script is committed to source control. SQL Compare can now use the script during deployment.
4. Deploy the migration script
Now we have created a migration script for the change, we can deploy it to WidgetStaging using SQL Compare:
- In SQL Compare, on the Project Configuration dialog box, select the latest version of WidgetDev as the source, andWidgetStaging as the target:
Click Compare Now.
The comparison results are displayed:You can see changes to WidgetDev that we will deploy to WidgetStaging.
The change to the table WidgetDescriptions is covered by the migration script we created.When SQL Compare is using a migration script to augment its own generated change script, this is indicated with the icon
- In the comparison results pane, make sure the check boxes for all objects with differences are selected:
- Click Deployment Wizard.
- On the first page of the Deployment Wizard, select a deployment method.
In this example we will deploy using SQL Compare.
- Click Next.
The Review migration scripts page of the wizard is displayed:The upper pane of the page lists the migration scripts you can select to include in the deployment.
The lower pane shows where in deployment SQL Compare will use the selected scripts.
In this example, the migration script we committed is listed, and included by default. - Click Next.
- The Review script page of the wizard is displayed, showing the generated deployment script.
The migration script we selected to include forms part of the deployment script.
- Click Deploy Now.
- A confirmation dialog box is displayed. Click Deploy Now to continue.
SQL Compare runs the deployment.
The comparison results are displayed, showing that WidgetDev and WidgetStaging are now the same. The columnWidgetName now has a default value, and no data was lost in the deployment.
The migration script we created will be re-used in future deployments, so there is no need to manually add the default values each time you deploy.