Database Publishing Wizard

Posted on 2010-04-08
Medium Priority
Last Modified: 2012-05-09
So I want to formalize the creation of the databases, in entirety.  I've got a million scripts per database -- the db itself, tables, procs, views, users, perms, etc -- but, i want one script per database, inclusive of everything, in entirety. And - I want it done in my order.  :-)

You go into SSMS, choose database\Tasks\Generate Scripts -- there's no real order to how things are scripted.  Or maybe I'm doing it wrong -- but, I always have to move stuff around to get it into my order -  somewhat like this:

  privilege grants

Alphabetically within that would be fabulous, but.....  not necessarily required.  :-)
(I know... that sounds pretty anal.)

Anyway, I want one script within which the entire database is scripted, from the ground up.  The database, all of the objects, the logins/users and associated privilege grants. Data isn't necessary.

I've never used the Database Publishig Wizard, so I'm doing a little reading, and I see this ability to script passwords along with the .sql, and I figured I'd check that out. I downloaded what i thought was the latest and greatest:

but the darned thing won't touch my v2008 instances.  
   'This SQL Server version (10.0) is not supported. (Microsoft.SqlServer.ConnectionInfo)

Maybe that's not the tool to use.  Maybe there's a more clever way to do the scripting within SSMS, or even Visual Studio.  

Given the above requirements, does anybody know of a good tool out there?  Or, need I just do what I've been doing via SSMS, and reorder all my objects in the final script, as needed?

Tips, thoughts, suggestions, magic sql --- all seriously welcome.  
Question by:dbaSQL
  • 2
  • 2
LVL 10

Expert Comment

ID: 30114090

In SSMS if you right click on a database, go to Tasks\Generate Scripts you can script almost any aspect into one file. Not sure if it does passwords though I've only used it for DDL and not security objects.
LVL 17

Author Comment

ID: 30115508
Yes, as I said, I've gone thru the SSMS 'Generate Scripts' many times.  I'm just looking for either a better tool, or another means of using this one, that will give me the output, as desired.
LVL 10

Accepted Solution

Bodestone earned 1400 total points
ID: 30116517
Sorry, I missed the very top bit.

I know it looks very un-ordered but it is basically in order of possible dependence on the object level so all the tables are added before any constraints, FKs and so on. within each group of objects i am not sure what determines the order.

I have seen 3rd party ones in the past but the ordering was roughly the same. ApexSQL Script is one I haven't had a chance to play with yet but if it's as good as their editor and Doc tool then it's at least worth having a go at the free trial.
LVL 17

Author Comment

ID: 30123858
i guess i was hoping for a little trick or two, that i was unaware of.
no biggie... i'll just dig back into SSMS.
thanks for looking

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question