Publishing a SQL Database
The Database Publishing Wizard in Visual Studio 2008 enables you to deploy a SQL Server database (both schema and data) to a hosting environment. You can run the wizard by right-clicking a database in Server Explorer and then clicking Publish to provider.
The tool supports the following ways to deploy a database:
It can generate a single SQL script file that you can manually run on the target server to re-create the database schema and the database contents.
It can connect to a Web service that is provided by your hosting environment and directly copy the contents of a source database into a target database.
Creating a Script File
The wizard's script mode enables you to point to a database on your local system and automatically create a script file (.SQL file). The script contains the information that is necessary to re-create the database on a remote system. This includes commands to re-create the database schema (tables, views, stored procedures, triggers, full-text catalogs, roles, rules, and so on). The script can also populate the new database with data from the local database.
The benefit of using a script file is that most hosting environments let you upload script files and run them by using a hosting administration control panel. If your hosting environment supports script files, you can use the wizard to deploy a database without requiring anything to be installed or configured by the hoster.
Note
Most database objects are scripted exactly as-is. However, there are some exceptions. Any non-system user accounts in the source database that are not based on Windows logins will be scripted as role entities. In addition, any objects that are encrypted cannot be scripted. If the source database contains encrypted objects, the wizard does not create a script.
Using a Web Service
The wizard's Web service mode enables you to use a Web service from a hosting environment to re-create a local database on the hosting site. The Web service mode requires that the hosting site support a SQL Publishing Web service. It also requires that the hosting site already have a database that you want to publish to. You do not have to create a script file to use the Web service mode.
Supported SQL Server Versions
The Database Publishing Wizard in Visual Studio 2008 supports all versions of Microsoft SQL Server 2000 and SQL Server 2005 except the Compact Edition as both source and target servers. The source and target database servers do not have to be the same version. However, the target database server must support all object and data types from the source database.
Permissions
In script mode, the Database Publishing Wizard creates commands for all objects that the current user has permissions to work with in the source database. The user account that you use to connect to the target database must have the required permissions to create all the objects from the source database. Typically, this means that you are running a user account that is a member of the db_ddladmin or db_owner roles.
Note
When you use Web service mode, the target database must be created before you use the wizard.
Wizard Pages
The Database Publishing Wizard consists of several pages that help you with the steps that are required to re-create a SQL database on a remote server. These steps are as follows:
Select Database page
Select an Output Location page
Select Publishing Options page
Review Summary page
Select Database
The Select Database page lets you select the database that you want to publish. The database must be attached to a running instance of SQL Server.
Select an Output Location
The Select an Output Location page lets you select between creating a script file and using a Web service that is provided by a hosting environment.
To create a script file, select Script to file and then enter a file name for the script file. You can also specify whether you want to overwrite any existing file that has the same name.
To publish to a database at a hosting environment by using a Web service, select Publish to shared hosting provider, and then select a hosting provider and target database. To change an existing provider, add a new provider, change a database, or add a new database, click More.
Select Publishing Options
The Select Publishing Options page lets you set options before you publish the database. The following table provides a description of each option that is listed in the drop-down lists.
Options |
Description |
Drop Existing Objects in Script |
Select True to overwrite existing objects that conflict with those that are being created. Select False not to overwrite existing objects. |
Schema Qualify |
Select True to generate two-part names that qualify object names with their schema name. Select False to generate one-part names that do not include the schema name. |
Script for target database |
Select the version of SQL Server for the target database. |
Types of data to publish |
Select Schema only, Data only, or Schema and data to specify options for generating the script. Schema refers to the object definitions in a database, and data refers to rows in the tables of the database. |
Review Summary
Use the Review Summary page to review the options that you have selected in the wizard.