Sql – How to automate the “generate scripts” task in SQL Server Management Studio 2008

automationsql serversql-server-2008ssmsvisual-studio-2008

I'd like to automate the script generation in SQL Server Management Studio 2008.

Right now what I do is :

  • Right click on my database, Tasks, "Generate Scripts…"
  • manually select all the export options I need, and hit select all on the "select object" tab
  • Select the export folder
  • Eventually hit the "Finish" button

Is there a way to automate this task?

Edit : I want to generate creation scripts, not change scripts.

Best Answer

What Brann is mentioning from the Visual Studio 2008 SP1 Team Suite is version 1.4 of the Database Publishing Wizard. It's installed with sql server 2008 (maybe only professional?) to \Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4. The VS call from server explorer is simply calling this. You can achieve the same functionality via the command line like:

sqlpubwiz help script

I don't know if v1.4 has the same troubles that v1.1 did (users are converted to roles, constraints are not created in the right order), but it is not a solution for me because it doesn't script objects to different files like the Tasks->Generate Scripts option in SSMS does. I'm currently using a modified version of Scriptio (uses the MS SMO API) to act as an improved replacement for the database publishing wizard (sqlpubwiz.exe). It's not currently scriptable from the command line, I might add that contribution in the future.

Scriptio was originally posted on Bill Graziano's blog, but has subsequently been released to CodePlex by Bill and updated by others. Read the discussion to see how to compile for use with SQL Server 2008.

http://scriptio.codeplex.com/

EDIT: I've since started using RedGate's SQL Compare product to do this. It's a very nice replacement for all that sql publishing wizard should have been. You choose a database, backup, or snapshot as the source, and a folder as the output location and it dumps everything nicely into a folder structure. It happens to be the same format that their other product, SQL Source Control, uses.

Related Topic