Using the Generate and Publish Scripts Wizard
You can use the Generate and Publish Scripts Wizard to create scripts for transferring a database from one instance of the Database Engine to another. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Azure. The generated scripts can be run on another instance of the Database Engine or SQL Azure. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services.
You can create scripts for an entire database, or limit it to specific objects.
Note
Functionality from the Publish Database Wizard has been added to the Generate and Publish Scripts Wizard. The Publish Database Wizard has been discontinued.
Supported Versions of SQL Server
The source database must be on an instance of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Azure. The target database must be on an instance of SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Azure.
Permissions
The minimum permission to publish a database is membership in the db_ddladmin fixed database role on the origin database. 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 on the target database.
The user also has 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.
Publishing to a Hosted Service
In addition to creating scripts, the Generate and Publish Scripts Wizard can be used to publish a database to a specific type of hosted SQL Server Web service. The SQL Server Hosting Toolkit provides Database Publishing Services as a shared source project on CodePlex. The Database Publishing Services project can be used by Web hosting providers to build a set of Web services that make it easy for their customers to deploy databases to the Web service. For more information about downloading the SQL Server Hosting Toolkit, see SQL Server Database Publishing Services.
Starting the Generate and Publish Scripts Wizard
To start the wizard, and generate a script, see How to: Generate a Script (SQL Server Management Studio).
To start the wizard and publish to a hosted SQL Server Web service, see How to: Publish a Database (SQL Server Management Studio).
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 Generate and Publish Scripts Wizard.
Database object |
SQL Server 2008 R2 |
SQL Server 2008 |
SQL Server 2005 |
SQL Server 2000 |
|---|---|---|---|---|
Application role |
Yes |
Yes |
Yes |
Yes |
Assembly |
Yes |
Yes |
Yes |
No |
CHECK constraint |
Yes |
Yes |
Yes |
Yes |
CLR (common language runtime) stored procedure1 |
Yes |
Yes |
Yes |
No |
CLR user-defined function |
Yes |
Yes |
Yes |
No |
Database role |
Yes |
Yes |
Yes |
Yes |
DEFAULT constraint |
Yes |
Yes |
Yes |
Yes |
Full-text catalog |
Yes |
Yes |
Yes |
Yes |
Index |
Yes |
Yes |
Yes |
Yes |
Rule |
Yes |
Yes |
Yes |
Yes |
Schema |
Yes |
Yes |
Yes |
No |
Stored procedure1 |
Yes |
Yes |
Yes |
Yes |
Synonym |
Yes |
Yes |
Yes |
Yes |
Table |
Yes |
Yes |
Yes |
Yes |
User2 |
Yes |
Yes |
Yes |
Yes |
User-defined aggregate |
Yes |
Yes |
Yes |
No |
User-defined data type |
Yes |
Yes |
Yes |
Yes |
User-defined function |
Yes |
Yes |
Yes |
Yes |
User-defined table |
Yes |
Yes |
No |
No |
User-defined type |
Yes |
Yes |
Yes |
No |
View1 |
Yes |
Yes |
Yes |
Yes |
XML schema collection |
Yes |
Yes |
Yes |
No |
1 Published without encryption.
2 Any non-system users that exist in the database are published as Roles.