dbaSQL
asked on
Database Publishing Wizard
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:
tables
indices
constraints
triggers
views
udf's
synonyms
procedures
logins
users
roles
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:
http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
but the darned thing won't touch my v2008 instances.
'This SQL Server version (10.0) is not supported. (Microsoft.SqlServer.Conne ctionInfo)
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.
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:
tables
indices
constraints
triggers
views
udf's
synonyms
procedures
logins
users
roles
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:
http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
but the darned thing won't touch my v2008 instances.
'This SQL Server version (10.0) is not supported. (Microsoft.SqlServer.Conne
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
no biggie... i'll just dig back into SSMS.
thanks for looking
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.