The MSDeploy SQL Database Provider (dbFullSql)

Overview

The MSDeploy SQL Database provider, also called dbFullSql is desgined for first-time installation or deployment of SQL databases to a specified destination database. This blog will serve more as a first step towards using the dbFullSql provider that recently shipped with the Beta 2 version of MSDeploy. The provider can be used in the following ways

1) Syncing a single source database to an empty or nonexistent destination database

2) Syncing a source database to an editable .sql file

3) Sync from a .sql file to an existing destination database.

Implementation

The dbFullSql provider uses SMO internally to generate the source database scripts and apply them to the target database. The following SMO Scripting Options are set to true by default inside the dbFullSql provider

a) ScriptSchema

b) ScriptData

c) DriAll

d) Triggers

e) Indexes

f) NoFileGroup

We support passing all SMO options, that take boolean values, as parameters to the dbFullSql provider. For more examples on how to use the SMO options with the dbFullSql provider, please check the command-line usage below. Since the script is only generated from the database specified as the source, it is not required to specify the SMO option as a parameter on the destination as well.

The dbFullSql provider will also create the target database if it does not exist. If you would like to drop the destination database and create a new one on sync, you can use the dropDestinationDatabase=true parameter on the destination

The dbFullSql provider also has transactional support during the execution of the SQL scripts on the destination database. This means, if there is an error in the middle of the sync, all the changes made to the destination database until then will be rolled back.

Command-line Usage

1) To sync a single source database (eg. SourceDatabase), to an empty or nonexistent destination database, you should run the following command

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<source db connection string>" -dest:dbFullSql="<dest db connection string>"

Example:

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true"

-dest:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=DestDatabase;Integrated Security=true"

2) To sync a source database (eg. SourceDatabase), to an editable .sql file

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<source db connection string>" -dest:dbFullSql="<absolutePath to a .sql file>"

Example:

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.;Initial Catalog=SourceDatabase;Integrated Security=true"

-dest:dbFullSql="d:\SourceDb.sql"

3) To sync a .sql file to an existing destination database

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<absolutePath to a .sql file>" -dest:dbFullSql="<dest db connection string>"

Example:

msdeploy.exe -verb:sync -source:dbFullSql="d:\SourceDb.sql"

-dest:dbFullSql="Data Source=SqlMachine\SQLEXPRESS;Initial Catalog=DestDatabase;User Id=user1;password=blah"

4)  To specify SMO options as parameters to the dbFullSql provider

Syntax:

msdeploy.exe -verb:sync -source:dbFullSql="<absolutePath to a .sql file>",smoOptionName=value -dest:dbFullSql="<dest db connection string>"

Example:

In order to skip scripting data on the source database and to sync only schema, you can run the following command

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true", ScriptData=false

-dest:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=DestDatabase;Integrated Security=true"

Example:

In order to include schema-qualified table references for foreign key constraints into the generated script, you can pass SchemaQualifyForeignKeysReferences=true to the dbFullSql provider

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true", SchemaQualifyForeignKeysReferences=false

-dest:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=DestDatabase;Integrated Security=true"

5) You could also sync a database to a package or an archive

Example:

a) msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true"

    -dest:archivedir="c:\archive"

b) msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\SQLEXPRESS;Initial Catalog=SourceDatabase;Integrated Security=true"

    -dest:package="c:\package.zip"

6) To sync multiple databases and database scripts to multiple databases, you can use a manifest file with the MSDeploy manifest provider.

Limitations

The dbFullSql provider cannot be used for incremental publishing to a target database. You can incrementally publish to a target database, only if there is no collision between existing objects on the source database and the target database.

The dbFullSql provider operates at the database level only and not at the SQL Server level. Hence any objects at the server level, like logins, on which objects at the database level depend on (like users) will not be scripted.

We currently do not support passing any non-boolean SMO option to the dbFullSql provider. 

References

For more information about SMO scripting options, you can refer this link

For information on how to download and install SMO and MSDeploy in general, you can refer the MSDeploy Help File