Friday, June 26, 2009

Database Publishing Wizard – Generate Script for Schema and Data

When working with databases we may get a requirement to generate the script for the schema and sometimes generate the script for data too. For generating script for Schema is what we will do usually in the projects, but generating the script for data is not everyone requirement. But sometimes we need to do that as well. So this article explains both generating the script for Schema and data.

There is a cool feature from Microsoft sql server team which will do this for us, called Database Publishing Wizard. It has the commands which supports for generating scripts for schema as well as data.

You can download it here.

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Examples: 

Command to run which will create schema and database:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql”

Command to run which will create schema:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -schemaonly

Command to run which will create data:
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks “C:\AdventureWorks.sql” -dataonly

Note: Don't try it on the databases which has the size more than 100MB, it will hang your computer for a while.

No comments:

Post a Comment