How can I automate the "generate scripts" task in SQL Server Management Studio 2008 ?

Question

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.

Answer 1

In Tools > Options > Designers > Table and Database Designers there's an option for 'Auto generate change scripts' that will generate one for every change you make at the time you save it.

Answer 2

You can do it with T-SQL code using the INFORMATION_SCHEMA tables.

There are also third-party tools - I like Apex SQL Script for precisely the use you are talking about. I run it completely from the command-line.

Answer 3

You can use SQL Server Management Object (SMO) to automate SQL Server 2005 management tasks including generating scripts: http://msdn.microsoft.com/en-us/library/ms162169.aspx.

Answer 4

If you want to a Microsoft solution you can try: Microsoft SQL Server Database Publishing Wizard 1.1

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

It create a batch process you can run anytime you need to rebuild the scripts.

Answer 5

If you're a developer, definitely go with SMO. Here's a link to the Scripter class, which is your starting point:

Scripter Class

Answer 6

From Visual Studio 2008 SP1 TeamSuite :

In the Server Explorer / Data Connections tab, there's a publish to provider tool which does the same as "Microsoft SQL Server Database Publishing Wizard", but which is compatible with MS Sql Server 2008.

Answer 7

where does it save?

Answer 8

yes its prompting for save script. but i want make settings at server level and generate scripts for all objects want to automate it. Is it possible.

Answer 9

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.