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 |
|
|
| 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.
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.
Step 1.3: On the Set Scripting Options page, select Save scripts to a specific location.
Select Save scripts to a specific location
Summary:
Click Finish.
To generate the Script for the Data on the table:
Set “Script Data: True “
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
Within Tables Object, I have chosen HumanResources.Employee Table
Select Save scripts to a specific location
Script Wizard Summary 
View of the Output file generated:
References:
Using the Generate and Publish Scripts Wizard
How to: Generate a Script (SQL Server Management Studio)
No comments:
Post a Comment