New-DlmDatabaseRelease

Compares two database schemas and creates database deployment resources that can be used to update one schema to the same state as the other.

Syntax

New-DlmDatabaseRelease -Source <Object> -Target <Object> [-FilterPath <string>] [-IgnoreStaticData] [-SQLCompareOptions <string>] [-TransactionIsolationLevel <TransactionIsolationLevel>] [-IncludeIdenticalsInReport] [-TemporaryDatabaseServer <DatabaseServerConnection>] [<CommonParameters>]

Description

The New-DlmDatabaseRelease cmdlet creates the database deployment resources containing all the information you need to update a target database schema to match a source database schema. The output can be used by the Use-DlmDatabaseRelease cmdlet to update the target database, or by the Export-DlmDatabaseRelease cmdlet to export a SQL change script.

The Source and Target parameters can be a database connection, a NuGet package, or a scripts folder.

The database deployment resources contain:

- a SQL update script that will update databases with the 'Target' schema to match the 'Source' schema

- a summary of the differences between the schemas

- any deployment warnings and metadata about the script.

If the Source parameter is a NuGet package or scripts folder containing tables flagged in SQL Source Control as static data tables and the Target parameter is a database connection object or string, DLM Automation will also compare the data in static data tables when generating the update script. To exclude static data changes, use the IgnoreStaticData parameter. For more information about static data, see http://www.red-gate.com/dlma/staticdata.

Filters: If the -Source parameter is a scripts folder or NuGet package that contains a filter definition (to include or exclude certain database objects), this will be applied when creating the update script and when performing pre- and post-deploy checks. You can specify an alternative filter using the FilterPath parameter. For more information about filters, see http://www.red-gate.com/dlma/filters.

Options: DLM Automation uses a set of default SQL Compare options when creating an update script and running pre- and post-deploy checks. To specify additional options or turn off any of the defaults, use the SQLCompareOptions parameter. For more information about SQL Compare options, see http://www.red-gate.com/dlma/compareoptions.

Parameters

`-Source <System.Object>`

The schema you want to update databases to. 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

Aliases	None
Required?	true
Position?	named
Default Value	None
Accept Pipeline Input	false
Accept Wildcard Characters	false

`-Target <System.Object>`

A database schema you want to update from. 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

- a list containing one or more of the above

If you use a list, the cmdlet will check that everything in the list has the same database schema. If there are no differences in schema, the cmdlet will create the Database Release. If there are any differences in schema, the cmdlet will fail and inform you.

Aliases	None
Required?	true
Position?	named
Default Value	None
Accept Pipeline Input	false
Accept Wildcard Characters	false

`-FilterPath <System.String>`

The path to a .scpf filter file.

Overrides any Filter.scpf file present in the Source schema with an alternative filter file to be used when generating the Update.sql change script and in all schema comparisons.

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

Aliases	None
Required?	false
Position?	named
Default Value	None
Accept Pipeline Input	false
Accept Wildcard Characters	false

`-IgnoreStaticData <System.Management.Automation.SwitchParameter>`

If you use Red Gate SQL Source Control, you can flag tables that contain static data. If you specify a scripts folder or NuGet package as the value of Source and a database connection object or string as the value of Target, by default DLM Automation compares the data in these tables when generating the update script. Use this parameter to ignore static data when comparing the databases.

Aliases	None
Required?	false
Position?	named
Default Value	False
Accept Pipeline Input	false
Accept Wildcard Characters	false

`-SQLCompareOptions <System.String>`

Specifies the SQL Compare options to use when creating the update script and running pre- and post-deploy checks. 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.

Aliases	None
Required?	false
Position?	named
Default Value	None
Accept Pipeline Input	false
Accept Wildcard Characters	false

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

The isolation level for the transactions used in the update script. 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

Aliases	None
Required?	false
Position?	named
Default Value	Serializable
Accept Pipeline Input	false
Accept Wildcard Characters	false

`-IncludeIdenticalsInReport <System.Management.Automation.SwitchParameter>`

By default, the change report will show the number of identical objects, but won't show the full SQL for each object. Use this parameter to include the full SQL of identical objects.

Aliases	None
Required?	false
Position?	named
Default Value	False
Accept Pipeline Input	false
Accept Wildcard Characters	false

`-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 (for example, Full-Text Search). In this case, or if LocalDB is not present, use this parameter to specifiy an alternative SQL Server instance for the temporary database.

Aliases	None
Required?	false
Position?	named
Default Value	None
Accept Pipeline Input	false
Accept Wildcard Characters	false

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

RedGate.DLMAutomation.Compare.Domain.DatabaseRelease

Examples

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

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test" -Username "sa" -Password "P@ssw0rd"

New-DlmDatabaseRelease -Source $test -Target $staging

This example show how to create the database deployment resources.

The New-DlmDatabaseConnection cmdlet creates two Database Connection objects, $staging and $test. $staging is used to connect to the Staging database on staging01\sql2014. $test is used to connect to the Test database on test01\sql2014.

The New-DlmDatabaseRelease cmdlet uses $test as the Source parameter, and $staging as the Target parameter. The cmdlet creates the database deployment resources that can be used to update the schema of Staging (and any database with the same schema as Staging) to match the schema of Test.

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

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"

$test = "Initial Catalog=Test; Data Source=test01\sql2014; User ID=sa; Password=P@ssw0rd"

New-DlmDatabaseRelease -Source $test -Target $staging

This example shows how to use a database connection string to specify the source schema.

A database connection string is used to specify the database, Test, as the source schema. The cmdlet only uses the Data Source, Initial Catalog, User ID, Password and Integrated Security properties of a database connection string. Any other properties specified in the string will be ignored.

You can also use a database connection string as the Target parameter. This specifies the schema of the databases you want to update.

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

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"

$package = "C:\NuGetFeed\database.nupkg"

New-DlmDatabaseRelease -Source $package -Target $staging

This example shows how to use a NuGet package to specify the source schema.

The New-DlmDatabaseRelease cmdlet uses a NuGet database package, database.nupkg, as the Source parameter. The package must contain a scripts folder in the db\state sub-folder.

If the NuGet package includes tables that have been flagged as containing static data, this data will be included when generating the Database Release, unless the IgnoreStaticData parameter is specified.

You can also specify a NuGet package as the Target parameter. However, if you do this, static data won't be deployed.

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

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"

$scriptsFolder = "C:\Work\scripts"

New-DlmDatabaseRelease -Source $scriptsFolder -Target $staging -IgnoreStaticData

This example show how to use a scripts folder to specify the source schema.

The New-DlmDatabaseRelease cmdlet uses a scripts folder, C:\Work\scripts, as the Source parameter. Scripts folders are a representation of a database's schema. They can be created by SQL Compare. If you're using SQL Source Control, your source control repository contains a scripts folder.

In this example, if the scripts folder includes tables that have been flagged as containing static data, this data will be ignored when generating the Database Release, because the -IgnoreStaticData parameter is specified.

You can also specify a scripts folder as the Target parameter. However, if you do this, static data will always be ignored in the deployment.

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

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"

$options = "IgnoreComments, ObjectExistenceChecks, -ForceColumnOrder"

New-DlmDatabaseRelease -Source $test -Target $staging -SQLCompareOptions $options

This example shows how to specify the SQL Compare options that are used when generating the update script and running pre- and post-deploy checks.

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

---------- EXAMPLE 6 ----------

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"

$filter = "C:\Work\MyFilter.scpf"

New-DlmDatabaseRelease -Source $test -Target $staging -FilterPath $filter

This example shows how to use a specific filter file to include/exclude database objects when generating the update script, overriding any filter file present in the source schema.

MyFilter.scpf defines which database objects are considered when the update script is generated. If this update is used, the filters will also be used in pre- and post-schema comparisons.

---------- EXAMPLE 7 ----------

$staging1 = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging1"
$staging2 = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging2"

$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"
  
New-DlmDatabaseRelease -Source $test -Target @($staging1, $staging2)

This example shows how to check that the schemas of several databases are the same, and then create a Database Release that can update any of the databases.

The Target parameter of the New-DlmDatabaseRelease cmdlet uses a list containing the Database Connections, $staging1 and $staging2. Before it creates the Database Release, New-DlmDatabaseRelease checks that the databases in the list have the same schema. This is useful for making sure the databases you want to deploy to are all in the same state.

If the Target databases are not in the same state, the cmdlet will fail. You must then either:

- run the New-DlmDatabaseRelease cmdlet for each database separately. This creates a separate Database Release for each database.

- update all the databases to the same state. You can then run the New-DlmDatabaseRelease cmdlet again using a list containing all the databases.

---------- EXAMPLE 8 ----------

$staging1 = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging1"
$staging2 = "C:\Work\scripts"

$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"
  
New-DlmDatabaseRelease -Source $test -Target @($staging1, $staging2)

This example shows how to specify different types of database schema source as the Target parameter.

The New-DlmDatabaseRelease cmdlet uses a list containing $staging1 and $staging2 as the Target parameter. $staging1 is a Database Connection object. $staging2 is a scripts folder. The New-DlmDatabaseRelease cmdlet checks that the database schemas are the same, and then creates the Database Release. If $staging2 contains a Filter.scpf filter file, this will be applied when checking that the database schemas are the same.

The list can contain a combination of any of the following: a Database Connection object; a database connection string; a NuGet package; a DatabasePackage object produced by the New-DlmDatabasePackage cmdlet; and a scripts folders.

If the Target parameter is a Database Connection object or string, and the Source parameter is a NuGet package or scripts folder, then static data will be included when generating the Database Release.

---------- EXAMPLE 9 ----------

$staging = New-DlmDatabaseConnection -ServerInstance "staging01\sql2014" -Database "Staging"
$test = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"

$temp = "Data Source=temp01"

New-DlmDatabaseRelease -Source $test -Target $staging -TemporaryDatabaseServer $temp

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

The New-DlmDatabaseRelease cmdlet uses 'Data Source=temp01' for the TemporaryDatabaseServer parameter. This indicates that the temporary database used by migrations v2 should be created on the 'temp01' server.