Deploying static data with SQL Change Automation PowerShell
Published 31 July 2019
This page shows how to deploy static data using the SQL Change Automation PowerShell for SQL Source Control projects.
Preparing the project for static data deployments
Before you can deploy static data changes with the PowerShell cmdlets, static data changes must be included in the SQL Change Automation compatible project to be deployed.
To see how to include static data in SQL Change Automation projects, see Static data.
To see how to include static data in SQL Source Control projects, see Static data (SQL Source Control documentation).
Deployment
By default, the SQL Change Automation cmdlets will synchronize the data in these tables between the source schema and target database.
To synchronize static data you must specify both the following as values for the New-DatabaseReleaseArtifact cmdet:
-Source: a path to a scripts folder or NuGet package containing a scripts folder
-Target: a database connection string or object created by the New-DatabaseConnection cmdlet
Static data is NOT supported when using system named constraints. To use static data you need to name your constraints.
Example
Create a connection to the Staging database we want to update, and specify the location of a NuGet package containing the latest version of the database:
$staging = New-DatabaseConnection -ServerInstance "staging01\sql2012" -Database "Staging" $package = "C:\Work\buildArtifacts\DatabaseBuildArtifact.1.0.0.nupkg"
The Staging database contains a static data table, Countries, with the following data:
| Id | Name |
|---|---|
| 1 | United States of America |
| 2 | India |
| 3 | Germany |
The NuGet package contains the same table but with different data:
| Id | Name |
|---|---|
| 1 | Brazil |
| 2 | India |
| 4 | UK |
Create the database deployment resources:
$release = New-DatabaseReleaseArtifact -Target $staging -Source $package
If we deploy this update to the Staging database:
- the name of country with id
1will be changed toBrazil Germanywill be removedUKwill be added
Make these changes so that static data between the package and the Staging database is identical:
Use-DatabaseReleaseArtifact $release -DeployTo $staging
The Use-DatabaseReleaseArtifact cmdlet runs two schema checks against static data - one before and one after running the update script:
- the pre-deploy schema check makes sure static data in the Staging database hasn't changed since the
New-DatabaseReleaseArtifactcmdlet was run - the post-deploy schema check makes sure static data in the Staging database has updated to the source schema in the package
Excluding static data changes
To exclude static data changes, add the -IgnoreStaticData parameter to the New-DatabaseReleaseARtifact cmdlet. This is useful if you don't want to deploy test data to a production environment.
