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.