Systems Engineering and RDBMS

Database Publishing Wizard for SQL Server 2005

Posted by decipherinfosys on May 16, 2008

Not many people have heard about the database publishing wizard for SQL Server. The wizard helps in the deployment of SQL Server 2005 databases – both the schema as well as the data into a shared hosting environment. The pre-requisites for this tool are the .Net 2.0 framework and SMO (SQL Server Management Objects). When you run it, whatever objects you have permissions on will be scripted and if there are encrypted objects (stored procedures, views, triggers etc.), those will be left out.

To begin with, you can download the database publishing wizard from here.  The default location of the install is: “C:\Program Files\Microsoft SQL Server\90\Tools\Publishing”.  Once you have installed it, bring up the command window and run:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d Decipher_Test “C:\DB_Scripts\Decipher\Decipher_Test.sql”

Change the names and the path accordingly.  You will see that it will generate a single script for both the schema as well as the data for the database: Decipher_Test.  If you want to generate only the schema or only the data, then you need to include those options, example:

For generating only the schema:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d Decipher_Test “C:\DB_Scripts\Decipher\Decipher_Test.sql” -schemaonly

And for generating only the data:

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d Decipher_Test “C:\DB_Scripts\Decipher\Decipher_Test.sql” -dataonly

There are many other options available for scripting as well including the GUI based scripter that you access from SQL Server Management Studio or the free ones like sqlscripter.com or one can write one’s own using SMO in order to have more flexibility like providing a list of tables to be scripted or a filter condition for some of the tables to script only a sub-set of the data etc.  We had written such a utility for one of our clients and we used it for scheduling the nightly builds for the client.

One Response to “Database Publishing Wizard for SQL Server 2005”

  1. CSIS Blogs said

    Distributing copies of SQL Server database…

    There are many ways to copy and distribute SQL server databases to multiple users. In an academic setting, you have to constrain the students’ access to just their database. That is, they usually don’t have enough privileges to create databases. In s…

Sorry, the comment form is closed at this time.

 
%d bloggers like this: