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.