Articles

Database Publishing Toolkit (Wizard)

A few notes if you plan to use this really helpful tool...

Scott Guthrie has written everything you'll need on his blog and you should read his article about the Database Publishing Toolkit.
For deployment of the database .sql file see his following article.

When does the Database Publishing Tool come handy?

Short answer: When working in shared hosting environments where the developer has limited access to the sql server database files.
The Database Publisher Tool generates a .SQL T-SQL TRANSACT file with the database schema AND data! You can now deploy that file either with SQLCMD or with the RunSQL.aspx file - for more information on how to do that consult the above mentioned page or the SQLCMD documentation on MSDN.

Note: The SQLCMD command can be run locally on your dev box IF you can remote connect to your server's SQL Server instance. Some shared hosting providers allow this, others don't. If you are not allowed to remote connect then you must use the RunSQL.aspx page because the only thing you need is a FTP connection to the server. Otherwise I prefer to use SQLCMD, either running on my dev box or running it of with a remote login on a VPS or Dedicated Server.

Some observations / issues

The generated .sql file contains all T-SQL commands

• - to re-create the database: e.g. create Tables, Views, Indexes, Keys, User, etc.
• - Furthermore it contains the INSERT commands for the actual data.

If you are using Primary / Foreign key dependencies between your tables you might run into error messages that tell you that a table has not yet been created, therefore another command could not be executed.
This happend in my test when the tool created some of my table views BEFORE writing the actual TABLE CREATE statement. The views could therefore not be created.
Solution: move the CREATE VIEW statements to a position in the .SQL file where all other resources have already been created.

Second observation I made: one of my Stored Procedures (sproc_A) referred via an EXEC statement to another Stored Procedure (let's call it sproc_B) which was placed by the Database Publishing Tool after the one that had the EXEC statement. While executing the .SQL file I got the error that sproc_B was not existent.
Simple solution again: just move sproc_B before the procedure where it is used.

Conclusion

It's a great tool to backup a database and deploy it again when working in shared shoting environments or if you just want to create a script of an existing db that contains the schema and data.
However, when dealing with tables that have dependencies via keys or if you have views then it is possible (but not necessarily) that the order of generated T-SQL statement is not correct and has to be modified. Not a big deal.


Cheers, Frank