Share via


Using the Publish Database Wizard

You can use the Database Publishing Wizard to transfer a database from a local computer to a Web-hosting provider. This wizard publishes a local database schema, with or without data.

Prerequisites for Installation

To use the Database Publishing Wizard on a client computer, the following components are required:

  • Microsoft .NET Framework 2.0

  • SQL Server Management Objects, SMO

These components are installed with SQL Server 2005 and SQL Server 2005 Client Tools; however, SQL Server 2005 is not required to be installed on the client computer. If the Database Publishing Wizard installer does not detect these components, see Installing the .NET Framework Documentation or Installing SMO.

Supported Versions of SQL Server

The source database must be on an instance of SQL Server 2005, SQL Server 2005 Express Edition, or SQL Server 2008. The target database must be on an instance of SQL Server 2000, SQL Server 2005, SQL Server 2005 Express Edition, or SQL Server 2008.

Permissions

The minimum permission to publish a database is membership in the db_ddladmin fixed database role. The minimum permission to publish a database script to an instance of SQL Server at the hosting provider is membership in the db_ddladmin fixed database role.

The user will also have to supply a user name and password to access their hosting provider account to publish with the wizard. The target database must be created at the hosting provider before the source database is published. Publishing overwrites objects in that existing database.

Starting the Publish Database Wizard

To start the Publish Database Wizard, in Object Explorer, right-click the database that you want to publish, point to Tasks, and then click Publish using Web Service.

Format Files

The Publish Database Wizard uses the files user.config and hoster.config to store configuration information. The directory in which these files are stored must have the appropriate NTFS file system permissions set. These files contain user names and encrypted passwords. The passwords are encrypted by using DPAPI.

user.config File

This file stores persisted host and configuration settings for the Publish Database Wizard. The user.config file is located at %SystemDrive%\Documents and Settings\%Username%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Publishing Wizard\user.config.

hoster.config File

This file stores options about Web service addresses, user names, and databases for shared hosting providers. The hoster.config file is located at %SystemDrive%\Documents and Settings\%Username%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Publishing Wizard\hoster.config.

Published Objects

The following table lists the objects that can be published and the versions of SQL Server on which they are supported by the Publish Database Wizard.

Database object

SQL Server 2008

SQL Server 2005

SQL Server 2000

Application role

Yes

Yes

Yes

Assembly

Yes

Yes

No

CHECK constraint

Yes

Yes

Yes

CLR (common language runtime) stored procedure1

Yes

Yes

No

CLR user-defined function

Yes

Yes

No

Database role

Yes

Yes

Yes

DEFAULT constraint

Yes

Yes

Yes

Full-text catalog

Yes

Yes

Yes

Index

Yes

Yes

Yes

Object

Yes

Yes

No

Rule

Yes

Yes

Yes

Schema

Yes

Yes

No

Stored procedure1

Yes

Yes

Yes

Synonym

Yes

Yes

Yes

Table

Yes

Yes

Yes

User2

Yes

Yes

Yes

User-defined aggregate

Yes

Yes

No

User-defined data type

Yes

Yes

Yes

User-defined function

Yes

Yes

Yes

User-defined table

Yes

No

No

User-defined type

Yes

Yes

No

View1

Yes

Yes

Yes

XML schema collection

Yes

Yes

No

1 Published without encryption.

2 Any nonsystem users that exist in the database will be published as Roles.