Web Deploy dbFullSql Provider

Applies To: Windows 7, Windows Server 2003, Windows Server 2003 R2, Windows Server 2008, Windows Server 2008 R2, Windows Vista, Windows XP

dbFullSql

The dbFullSql provider enables first-time publishing of databases from a local SQL Server development database to a remotely hosted SQL Server database.

Example

msdeploy.exe -verb:dump -source:dbFullSql="Data Source=.;Integrated Security=SSPI;Initial Catalog=Northwind" -xml

The dbFullSql provider takes an input that is either a path to a SQL script file or a connection string to a database. If the input is a connection string, it should be prefaced with Data Source=. For example, the following syntax specifies a local SQL database named db1 as the data source.

-source:dbFullSql="Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=db1"

The dbFullSql provider is useful if you are a developer who hosts your Web application databases on remote machines that are serviced by hosting providers or enterprise IT groups. Although you must have administrative privileges on your local development SQL Server, you do not necessarily have to have administrative privileges on the remote SQL Server. Before running the dbFullSql provider, you should have completed your testing in a development environment and be confident that application will work as expected.

The dbFullSql provider uses SQL Server Management Objects (SMO) to reverse engineer the source database into Transact-SQL DDL (Data Definition Language) and DML (Data Manipulation Language) scripts. You can review these scripts before running them on the target server. After the script is executed on the target database, a report of the actions applied to the target database is returned in XML format.

Prerequisites

The dbFullSql provider requires the following components, which must be installed manually.

• SQL Server Management Objects (SMO) v. 10.

• Microsoft Core XML Services (MSXML) 6.0

• Microsoft SQL Server 2008 Native Client (SQL Native Client)

Descriptions and download locations for these components follows.

SQL Server Management Objects (SMO)

The dbFullSql provider requires SQL Server Management Objects (SMO) to generate Transact-SQL script and DDL and DML for database publishing. SMO is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services. This object model will work with SQL Server 2000, SQL Server 2005 and SQL Server 2008. You can download the SQL Server 2008 Management Objects from the following locations.

X86 Package (SharedManagementObjects.msi) - 3225 KB

X64 Package (SharedManagementObjects_x64.msi) - 3895 KB

IA64 Package (SharedManagementObjects_ia64.msi) - 5640 KB

SMO in turn requires Microsoft Core XML Services (MSXML) 6.0 and the Microsoft SQL Server Native Client.

Microsoft Core XML Services (MSXML) 6.0

Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML. You can download MSXML 6.0 from the following location.

Microsoft Core XML Services (MSXML) 6.0

Microsoft SQL Server 2008 Native Client

Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) that contains both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications that use native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. You should use SQL Native Client to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API. You can download the SQL Native Client from the following locations.

X86 Package (sqlncli10.msi) - 4549 KB

X64 Package (sqlncli10_x64.msi) - 7963 KB

IA64 Package (sqlncli10_ia64.msi) - 11112 KB

Uses

The dbFullSql provider can be used for first-time publishing to a database. It cannot be used for incremental publishing unless there are no collisions between existing objects on the source database and the target database. The dbFullSql provider will create the target database if the target database does not exist. If you want to drop the destination database and create a new one during synchronization, add the provider setting ,dropDestinationDatabase=true to the -dest:dbfullsql argument.

dbFullSql provides transactional support during the execution of the SQL scripts on the destination database. If an error occurs during a sync operation, all changes made to the destination database will be rolled back.

To synchronize multiple databases and database scripts to multiple databases, you can use a manifest file with the Web Deploy manifest provider. For more information, see Web Deploy manifest Provider.

Provider Settings

The dbFullSql provider has 121 provider settings. Many of these settings correspond to the SMO ScriptingsOptions Properties that take Boolean values. Non-Boolean options are not supported.

The following table describes those dbFullSql scripting options that are found neither on the SMO ScriptingsOptions Properties page nor on the SMO Transfer Properties page.

dbFullSql Scripting Option	Description
commandDelimiter	A user-defined character or string of characters that separates commands in a source SQL script that synchronizes to a destination database. This setting has no effect in a "script-out" scenario (that is, when a SQL script is the destination).
commandTimeout	An integer that specifies the time, in seconds, that Web Deploy waits for the dbFullSQL provider to complete its operations. The default value is 30.
createDBConnectionString	A connection string used to create the database on the destination if the database specified in the source does not exist on the destination. If the destination database does not exist and no value has been specified for createDBConnectionString, dbFullSql will attempt to create it by using the connection string specified in the path.
dropDestinationDatabase	A Boolean value. True if the destination database will be dropped and new database created at synchronization time; otherwise, false. The default is false.
forceScriptDatabase	This setting is for infrastructure support and is not intended to be used directly. The default is false.
IncludeData	A Boolean value that indicates whether data is scripted. True if data is scripted; otherwise, false. This setting is equivalent to the SMO scriptData scripting property.
IncludeSchema	A Boolean value that indicates whether the schema is scripted. True if the schema is scripted; otherwise, false. This setting is equivalent to the SMO scriptSchema scripting property.
ObjectList	A semi-colon delimited list of SQL objects that you want to script from the database. By default, all objects in the database are scripted. You can use this provider setting to specify individual tables in a database. See the Example Usages section for an example.
removeCommandDelimiter	A Boolean value. Set removeCommandDelimiter to true if you want the delimiter specified by the commandDelimiter setting to be removed from a source SQL script before the script is run against a destination database connection. If the delimiter that you have specified is not part of the SQL language, you must remove it from the script before executing the script (for example, "GO" is not part of the SQL language). This setting has no effect in a "script-out" scenario (that is, when a SQL script is the destination). The default is true.
ScriptDropsFirst	A Boolean value. True if drop statements will be generated first; otherwise, false. The default scripting behavior is for the schema to be scripted. The script generated will work on a new or empty destination database. However, if you want to first generate the drop statements for the schema so that you can run the script on any destination database even if the database already has part of the schema present, you can set this value to true. The default is false. Warning The ScriptDrops and ScriptDropsFirst provider settings are incompatible. If you specify both at the same time, an error will result.
sqlCe	A Boolean value. True if the source is a SQL Server Compact database; otherwise, false. For more information, see the section "Using the dbFullSQLProvider to synchronize SQL Server Compact to SQL Server" on this page.
storeConnectionStringPassword	A Boolean value. True if the password contained in the connection string will be stored when the database is scripted to a package or archive; otherwise, false. This setting applies only if you use a connection string that has a password in it. For security reasons, passwords are removed by default from connection strings when a database is scripted to a package or archive. However, the resulting package cannot be scripted back into a database because the connection string will no longer contain the password. The default is false.
transacted	A Boolean value. True if all changes made to the destination database will be rolled back if an error occurs during synchronization; otherwise, false. You may need to set this option to false if your script contains full-text catalogs or other commands that cannot be transacted. The default is true. Important The transacted setting applies only when the script is the source of the command. It does not apply when you specify a script as the destination (that is, when the script is the output created from an existing database source).

Using Web Deploy with SQLCMD scripts that have variables

In version 1.1 of the Web Deploy, you can use Web Deploy with SQL scripts that define scripting variables with the :setvar command. An example script follows.

:setvar databaseName TestDatabase
:setvar tableName TestTable
GO
Create database $(databaseName)
GO
create table $(tableName)(name varchar(50))
GO
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
GO

In the example script, $(databaseName) will be replaced with TestDatabase, and $(tableName) will be replaced with TestTable. To use the variables as they are defined in the script, run a command like the following example.

msdeploy -verb:sync -source:dbfullsql="C:\sqlcmd.sql" -dest:dbfullsql="data source=contoso03-iis;initial catalog=testdatabase1;integrated security=true;"

If you want to replace the values of the variables in the script with new values when you run the command, use the setParam argument, set its parameter type to SqlCommandVariable, and provide appropriate values for the scope, match, and value arguments, as in the following example.

msdeploy -verb:sync -source:dbfullsql="C:\sqlcmd.sql" -dest:dbfullsql="data source=contoso03-iis;initial catalog=testdatabase1;integrated security=true;" -setParam:kind=SqlCommandVariable,scope=<FileName>,match=<VariableName>,value=<NewValue>

For more information about setParam, see Using declareParam and setParam.

Using the UI to export and import packages that contain SQLCMD scripts that have variables

When you export a package by using the IIS Manager UI, and the package contains a SQL script with scripting variables, Web Deploy will automatically create parameters for the scripting variables that you created. When you later import the package, you can change the values of the script variables by changing the parameters that Web Deploy generated for you. For more information, see SQLCMD scripts in Web Deploy 1.1.

Using the dbFullSQLProvider to synchronize SQL Server Compact to SQL Server

In version 2.0 of Web Deploy, you can use the dbFullSql provider to synchronize a SQL Server Compact database to SQL Server, or to synchronize a SQL Server Compact database to a script for a SQL Server database.

There are two ways to indicate in a Web Deploy operation that the source is a SQL Server Compact database. First, you can simply put the file path of the SQL Server Compact file for Data source in the source argument. If your Data source connection string argument does not specify the User Id, Initial catalog, Integrated security, or Trusted Connection arguments, and instead specifies a file path, the source will be treated as a SQL Server Compact database. The following example shows this first method.

msdeploy -verb:sync -source:dbFullSql="Data source=c:\databases\northwind.sdf" -dest:dbfullSql="Data source=.\sqlexpress;integrated security=SSPI;initial catalog=MyNewDB"

The second way to indicate to Web Deploy that the source is a SQL Server Compact database is to explicitly specify sqlCe=true in the command, as in the following example.

msdeploy -verb:sync -source:dbFullSql="Data source=c:\databases\northwind.sdf",sqlCe=true -dest:dbfullSql="Data source=.\sqlexpress;integrated security=SSPI;initial catalog=MyNewDB"

Example usages

1) Publish the SQL database application db1 in its entirety to db2.

msdeploy.exe -verb:sync -source:dbFullSql="Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=db1" -dest:dbFullSql="Data Source=.\sqlexpress;Integrated Security=SSPI;initial catalog=db2;"

2) Archive a full source database script, including schema and data, by using the archiveDir provider.

msdeploy.exe -verb:sync -source:dbFullSql="Integrated Security=SSPI;Initial Catalog=source_database;Data Source=.\sqlexpress;",ScriptData=true -dest:archiveDir=c:\archive

3) Add the full source database script, including schema and data, to a Web application package for later deployment by using the package provider.

msdeploy.exe -verb:sync -source:dbFullSql="Integrated Security=SSPI;Initial Catalog=source_database;Data Source=.\sqlexpress;",ScriptData=true -dest:package=c:\package.zip

4) Synchronize the SourceDatabase database to the empty or nonexistent destination database called DestDatabase.

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"

5) Synchronize the SourceDatabase database to an editable .sql file.

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

6) Synchronize a .sql file to the existing destination database called DestDatabase.

msdeploy.exe -verb:sync -source:dbFullSql="d:\SourceDb.sql" -dest:dbFullSql="Data Source=SqlMachine\SQLEXPRESS;Initial Catalog=DestDatabase;User Id=user1;password=zyxz"

7) Script the schema, but not the data, from the source database by setting the SMO ScriptData option to false.

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"

8) Include schema-qualified table references for foreign key constraints by setting the SMO SchemaQualifyForeignKeysReferences option to true.

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"

9) Use the ObjectList provider setting to synchronize only Table1 and Table2 from the source database to the MyTables.sql file.

msdeploy -verb:sync -source:dbfullsql="Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=SourceDatabase",ObjectList=Table1;Table2 -dest:dbfullsql=d:\MyTables.sql

See Also

Web Deploy archiveDir Provider

Web Deploy filePath Provider

Web Deploy manifest Provider

Web Deploy package Provider