Monday, May 16, 2011

How to create scripts for an entire database or specific object in SQL Server 2008?


In this post I would like to address the requirement of;

  • How to create scripts for an entire database ?
  • How to create scripts limit it to specific objects?
  • How to restore SQL Server 2005 database on SQL Server 2000?

SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server.

It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window.

If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

We 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. 

We can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services.

We can create scripts for an entire database, or limit it to specific objects.

What version of SQL Servers are supported on this?

  Source Database Target Database 
SQL Server Instance Version Supported
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Azure
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Azure
  • SQL Server 2000
Permission needed db_ddladmin db_ddladmin
Pre-existence   The target database must be created at the hosting provider before the source database is published.
Publishing overwrites objects in that existing database

Supported Objects for Publishing

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

 

1 Published without encryption.
2 Any non-system users that exist in the database are published as Roles.

How to generate SQL Scripts using Generate and Publish Scripts Wizard

We can create Transact-SQL scripts for multiple objects by using the Generate and Publish Scripts Wizard.. You can also generate a script for individual objects or multiple objects by using the Script as menu in Object Explorer.

Use the Generate and Publish Scripts Wizard to create a Transact-SQL script for many objects.
The wizard generates a script of all the objects in a database, or a subset of the objects that you select. The wizard has many options for your scripts, such as whether to include permissions, collation, constraints, and so on.

Step 1:

In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.

image

image 

Step 2: On the Choose Objects page, select the objects to be included in the script


Script entire database and all database objects
Click to generate scripts for all objects in the database and to include a script for the database itself.

image

Step 1.3: On the Set Scripting Options page, select Save scripts to a specific location.

image

Select Save scripts to a specific location

image
Summary:

image

Click Finish.

To generate the Script for the Data on the table:

Set “Script Data: True “

 image

Select specific database objects
Click to limit the wizard to generate scripts for only the specific objects in the database that you choose.

For example I have selected Tables Object

 image

Within Tables Object, I have chosen HumanResources.Employee Table

image 

Select Save scripts to a specific location

image

Script Wizard Summary
image 

image

View of the Output file generated:

image 

References:

Using the Generate and Publish Scripts Wizard

How to: Generate a Script (SQL Server Management Studio)

No comments: