Deploying a Database by Using the Database Publishing Wizard
The Database Publishing Wizard in Visual Studio 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 provider and directly copy the contents of a source database into a target database. If you want to use this method, verify that your hosting provider supports connections from the Database Publishing Wizard.
If you are deploying a database to a production server, you might want to prevent the Web site from responding to page requests during the deployment process. This can help avoid errors that might result during the time when changes are only partially completed. For more information, see How to: Prepare to Deploy a Web Project.
Note
This topic applies only to Web site projects. For information about the difference between Web application projects and Web site projects, see Web Application Projects versus Web Site Projects in Visual Studio.
Creating a Script File
The wizard's script mode enables you to specify a database on your local computer and automatically create a script file (.SQL file). The script contains the information that is necessary to re-create the database on a remote computer. The script 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 include commands that populate the new database with data that matches 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 in order to use the Web service mode.
Supported SQL Server Versions
The Database Publishing Wizard supports Microsoft SQL Server 2000 and SQL Server 2000 Express and later versions. (The wizard does not support SQL Server Compact.) 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 already exist before you use the wizard.
Wizard Pages
The Database Publishing Wizard displays a dialog box for each step that is required in order to publish a SQL Server database. These dialog boxes are as follows:
The Select Database dialog box
The Select an Output Location dialog box
The Select Publishing Options dialog box
The Review Summary dialog box
Select Database
The Select Database dialog box 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 dialog box lets you select between creating a script file and using a Web service that is provided by a hosting provider. If you choose to publish to a database at a hosting provider by using a Web service and you want to change a 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 the following options before you publish the database.
Option |
Description |
Drop existing objects in script |
Select True to overwrite existing objects that conflict with those that are being created. Select False to not 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.
See Also
Concepts
ASP.NET Web Site Project Deployment Overview