Use-DlmDatabaseRelease

Uses the DatabaseRelease object produced by the New-DlmDatabaseRelease cmdlet to update a target database schema to match the source schema.

Syntax

Description

The Use-DlmDatabaseRelease cmdlet executes the SQL update script inside the database deployment resources produced by the New-DlmDatabaseReleaseCommand cmdlet. This updates the target database to the same state as the schema source specified in the DatabaseRelease object.

Before executing the script, DLM Automation first checks that the target database schema matches the target schema defined in the DatabaseRelease object. If the schemas don't match, DLM Automation will then check if the target database schema matches the source schema specified in the Use-DlmDatabaseRelease cmdlet. (This is to check if the update has already been applied and the target schema is already in the desired state). If these schemas also don't match, the cmdlet fails. If the checks show the target schema is already in the desired state, DLM Automation will skip running the SQL update script and the cmdlet will finish. To turn off these checks, use the SkipPreUpdateSchemaCheck parameter.

The Use-DlmDatabaseRelease cmdlet then executes the SQL script to update the target database. It logs any errors that occur in the execution, but will continue executing the whole script unless the connection to the database is broken (for example, if the database goes offline).

After executing the script, the cmdlet checks that the target database schema has updated correctly to the 'Source' schema specified in the New-DlmDatabaseRelease cmdlet. If the schema hasn't updated correctly, the cmdlet returns an error message. To turn off this check use the SkipPostUpdateSchemaCheck parameter.

By default, DLM Automation deploys static data contained in a NuGet package, scripts folder or zip file; no data will be deployed from a live data source. The pre-update and post-update schema checks also check for differences in static data. To turn off the deployment of static data and to exclude static data from the schema checks, add IgnoreStaticData to the New-DlmDatabaseRelease command. For more information about static data, see http://www.red-gate.com/dlma/staticdata.

If the database deployment resources contain a filter, this will be applied to the pre-deployment and post-deployment schema checks; only differences between objects included by the filter will cause the check to fail. For more information about filters, see http://www.red-gate.com/dlma/filters.

The SQL Compare options specified in the DatabaseRelease object will be used in the schema comparison checks before and after running the update. For more information about SQL Compare options, see http://www.red-gate.com/dlma/compareoptions.

Parameters

-InputObject <RedGate.DLMAutomation.Compare.Domain.ReleaseArtifacts.IReleaseArtifact>

The Release Artifact to be deployed.

-DeployTo <RedGate.DLMAutomation.Compare.SchemaSources.DatabaseConnection>

A Database Connection object or database connection string that identifies the target database to be updated. See New-DlmDatabaseConnection for details.

-SkipPreUpdateSchemaCheck <System.Management.Automation.SwitchParameter>

Before running the update script, don't check that the target database has the correct schema or that the target database has already been updated.

-SkipPostUpdateSchemaCheck <System.Management.Automation.SwitchParameter>

Don't check that the target database has the correct schema after the update has run.

-QueryBatchTimeout <System.Int32>

The execution timeout, in seconds, for each batch of queries in the update script. The default value is 30 seconds. A value of zero indicates that no execution timeout will be enforced.

-AbortOnWarningLevel <RedGate.DLMAutomation.Shared.Domain.WarningSeverity>

Use this parameter to set the minimum warning level that will cause the release execution operation to abort.

Valid warning severity levels are:

- High

- Medium

- Low

- Information

- None (do not abort for any warnings)

The default setting is 'None'.

Possible values: Information, Low, Medium, High, None

-DisableMonitorAnnotation <System.Management.Automation.SwitchParameter>

Don't add annotations to the event log for SQL Monitor.

<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable. For more information, see http://technet.microsoft.com/en-us/library/hh847884.aspx.

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

• RedGate.DLMAutomation.Compare.Domain.ReleaseArtifacts.IReleaseArtifact

  The Release Artifact to be deployed.

Return values

The output type is the type of the objects that the cmdlet emits.

• None

Examples

---------- EXAMPLE 1 ----------

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging" -Username "AutomationUser" -Password "P@ssw0rd"
$package = "C:\Work\packages\MyDatabase.1.0.0.nupkg"

$update = New-DlmDatabaseRelease -Source $package -Target $staging

Use-DlmDatabaseRelease $update -DeployTo $staging

This example shows how to update a database to the schema contained in a NuGet package, MyDatabase.1.0.0.nupkg.

The New-DlmDatabaseRelease cmdlet creates the database deployment resources. It uses the database, Staging, as the Target parameter: this is the schema to be updated. It uses a NuGet package containing a scripts folder, database.nupkg, as the Source parameter: this is the schema that Staging will be updated to.

The DatabaseRelease object, $update, is then passed to the Use-DlmDatabaseRelease cmdlet. This cmdlet runs the SQL update script contained in $update, which updates Staging to the schema contained in database.nupkg.

Before running the SQL update script, Use-DlmDatabaseRelease checks that Staging still has the same schema that it did at the time that $update was created. It then runs the script, and afterwards, checks that the update was successful and that Staging has the correct new schema.

If $package contains static data, DLM Automation will deploy it to the $staging database. The pre-deployment and post-deployment schema checks also check for differences in static data. To turn off the deployment of static data and to exclude static data from the schema checks, add the IgnoreStaticData parameter to New-DlmDatabaseRelease.

---------- EXAMPLE 2 ----------

$production = New-DlmDatabaseConnection -ServerInstance "prod01\sql2014" -Database "Production" -Username "AutomationUser" -Password "P@ssw0rd"
$build = Import-DlmDatabasePackage "C:\Work\packages\DatabaseBuildArtifact.1.0.0.nupkg"

$update = New-DlmDatabaseRelease -Source $build -Target $production

Use-DlmDatabaseRelease $update -DeployTo $production

This example shows how to update a database to the schema contained in a Redgate Database Build Artifact, DatabaseBuildArtifact.1.0.0.nupkg.

This cmdlet runs the targeted deployment script contained in the RedgateDatabaseReleaseArtifact, which updates the database "Production".

---------- EXAMPLE 3 ----------

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging" -Username "AutomationUser" -Password "P@ssw0rd"
$package = "C:\Work\packages\MyDatabase.1.0.0.nupkg"

$update = New-DlmDatabaseRelease -Source $package -Target $staging

Use-DlmDatabaseRelease $update -DeployTo $staging -SkipPostUpdateSchemaCheck

This example shows how to skip the post-deployment check that verifies the target database matches the source.

The Use-DlmDatabaseRelease cmdlet updates the database, Staging, to match the schema contained in the NuGet package, database.nupkg. The SkipPostUpdateSchemaCheck parameter is used to skip the post-update schema check.

---------- EXAMPLE 4 ----------

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging" -Username "AutomationUser" -Password "P@ssw0rd"

Import-DlmDatabaseRelease -Path "C:\Work\DatabaseRelease" | Use-DlmDatabaseRelease -DeployTo $staging

This example shows how to use the Import-DlmDatabaseRelease cmdlet to import the database deployment resources from a folder created previously by the Export-DlmDatabaseRelease cmdlet. The database, Staging, is updated to the schema specified by the DatabaseRelease object.