SQL Source Control 4

Example - deploying with migration scripts

Page last updated 22 July 2015

This example shows you how to create a migration script for a database so you can deploy automatically without fear of data loss.

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:

  1. Set up the databases
  2. Specify a migration scripts location
  3. Create a migration script
  4. Deploy the migration script

Requirements

The example uses:

1. Set up the databases

This example uses the WidgetDev and WidgetStaging databases.

To create the databases on your SQL Server:

  1. 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
  2. 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:

  1. Make sure WidgetDev is selected in the Object Explorer.
  2. 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.

  3. 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.

  4. Click Create Folder.

    The Create Folder dialog box is displayed.

  5. In Folder Name, type a name for the folder.

    In this example, call the folder WidgetMigrations

  6. Click Create.

    The new folder is displayed on the Select Migration Scripts Repository dialog box.

  7. Make sure the Widget Migrations folder is selected, and click Select.

    The new migration scripts location is selected

  8. 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:

  1. 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:

  2. 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.

  3. Before the final ALTER TABLE statement in the script, add the following SQL:

    UPDATE [WidgetDescriptions] SET WidgetName = '<Unset>' WHERE WidgetName IS NULL

GO
  4. Click Proceed to commit.
    SQL Source Control displays a confirmation dialog box:

  5. Click Save.
    The migration script is now listed on the Commit Changes tab:

  6. 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:

  1. 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 

  2. In the comparison results pane, make sure the check boxes for all objects with differences are selected:

  3. Click Deployment Wizard.
  4. On the first page of the Deployment Wizard, select a deployment method.

    In this example we will deploy using SQL Compare.

  5. 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.

  6. Click Next.
  7. 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.

  8. Click Deploy Now.
  9. 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.

Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal