Sync-DlmDatabaseSchema

Updates a target database schema to match a source database schema.

Syntax

Description

The Sync-DlmDatabaseSchema cmdlet updates a target database to match a source database schema.

The source schema can be a database connection, a NuGet package, or a scripts folder. The target schema must be a database connection.

Parameters

-Source <System.Object>

The source database schema used to update the target database. This can be:

- a Database Connection object created by the New-DlmDatabaseConnection cmdlet

- a database connection string

- a path for a NuGet package or .zip file. This must contain a scripts folder located at db\state

- a Database Package object produced by the New-DlmDatabasePackage cmdlet

- a path for a scripts folder, created by SQL Compare or from your SQL Source Control database repository

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

The target database to be updated. This can be:

- a Database Connection object created by the New-DlmDatabaseConnection cmdlet

- a database connection string

-FilterPath <System.String>

The path to a .scpf filter file.

Use this parameter to specify a filter file to be used when performing the sync operation. This will override any Filter.scpf file in the source.

-IgnoreStaticData <System.Management.Automation.SwitchParameter>

When DLM Automation performs a sync operation and the source is a NuGet package or a scripts folder, by default it will include static data flagged by SQL Source Control.

If you specify this parameter, static data will be ignored.

-SQLCompareOptions <System.String>

Specifies the SQL Compare options to use when performing the sync operation. DLM Automation applies a default set of options (listed below). To include additional options, specify a comma-delimited list of the option names (eg 'IgnoreComments, ObjectExistenceChecks'). To turn off a default option, precede the option name with a minus sign (eg '-ForceColumnOrder').

This parameter will be ignored if the value specified is $null or empty.

By default, the following Compare options are used:

- ConsiderNextFilegroupInPartitionSchemes

- DecryptPost2KEncryptedObjects

- DoNotOutputCommentHeader

- ForceColumnOrder

- IgnoreCertificatesAndCryptoKeys

- IgnoreDatabaseAndServerName

- IgnoreTSQLT

- IgnoreUsersPermissionsAndRoleMemberships

- IgnoreUserProperties

- IgnoreWhiteSpace

- IgnoreWithElementOrder

- IncludeDependencies

- ThrowOnFileParseFailed

- UseCompatibilityLevel

- UseMigrationsV2

For more information about SQL Compare options, see http://www.red-gate.com/dlma/compareoptions.

-TransactionIsolationLevel <RedGate.DLMAutomation.Shared.Domain.TransactionIsolationLevel>

Use this parameter to specify the isolation level for the transactions during the sync operation. Permitted values are: Serializable, Snapshot, RepeatableRead, ReadCommitted and ReadUncommitted. The default level is Serializable.

See http://msdn.microsoft.com/en-gb/library/ms173763.aspx for more details on transaction isolation levels.

Possible values: Serializable, Snapshot, RepeatableRead, ReadCommitted, ReadUncommitted

-TemporaryDatabaseServer <RedGate.DLMAutomation.Compare.SchemaSources.DatabaseServerConnection>

The connection string for the temporary database server used by Migrations V2 beta. For example, 'Data Source=TempServer01'.

When using Migrations V2 beta, DLM Automation needs to create a temporary database. DLM Automation will automatically use LocalDB if it's installed. However there may be some features in your database that aren't supported by LocalDB, such as Full-Text Search. In this case, or if LocalDB isn't present, use this parameter to specify an alternative SQL Server instance for the temporary database.

-QueryBatchTimeout <System.Int32>

The execution timeout, in seconds, for each batch of queries during the sync operation. 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 sync operation to abort.

Valid warning severity levels are:

- High

- Medium

- Low

- Information

- None (do not abort for any warnings)

The default setting is 'High'.

This parameter can't be used in addition to the Force parameter.

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

-Force <System.Management.Automation.SwitchParameter>

Use this parameter to prevent the sync operation from aborting regardless of any warnings.

An alternative way of doing this is to set the AbortOnWarningLevel to 'None'.

This parameter can't be used in addition to the AbortOnWarningLevel parameter.

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

• None.
  You cannot pipe input to this cmdlet.

Return values

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

• None

Examples

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

$scriptsFolder = "C:\Work\scripts"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd"

Sync-DlmDatabaseSchema -Source $scriptsFolder -Target $test

This example show how to update a test database to match a scripts folder.

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

$validatedScriptsFolder = Invoke-DlmDatabaseSchemaValidation "C:\Work\scripts"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd"

Sync-DlmDatabaseSchema -Source $validatedScriptsFolder -Target $test

This example shows how to update a test database to match a validated scripts folder.

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

$scriptsFolder = "C:\Work\scripts"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd"

Sync-DlmDatabaseSchema -Source $scriptsFolder -Target $test -AbortOnWarningLevel Medium

This example shows how to abort the update if there are any warnings at medium level or above.

Set the -AbortOnWarningLevel parameter to 'None' or use the Force parameter if you want the cmdlet to ignore all warnings.

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

$scriptsFolder = "C:\Work\scripts"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd"

$options = "IgnoreComments, ObjectExistenceChecks, -ForceColumnOrder"

Sync-DlmDatabaseSchema -Source $scriptsFolder -Target $test -SQLCompareOptions $options

This example shows how to specify SQL Compare options to use when performing the sync operation.

In this example, the $options variable specifies that the IgnoreComments and ObjectExistenceChecks options should be included in addition to the default set of SQL Compare options. The minus sign before the ForceColumnOrder parameter indicates that this default option will be turned off.

---------- EXAMPLE 5 ----------

$scriptsFolder = "C:\Work\scripts"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd"

$temp = "Data Source=temp01"

Sync-DlmDatabaseSchema -Source $scriptsFolder -Target $test -TemporaryDatabaseServer $temp

This example shows how to use a connection string to specify the temporary database server used for Migrations v2.

The Sync-DlmDatabaseSchema cmdlet uses "Data Source=temp01" for the TemporaryDatabaseServer parameter. This means the temporary database used by Migrations v2 will be created on the "temp01" server.