How do I generate a database schema in a job?
-
Our system administrator would like us to have a job which will generate the database schema, for all of our user databases, on a monthly basis. I've generated the schemas for tables, view and SP's, but only through the SSMS GUI. I don't really know how to do this through a job. So, in order to satisfy him, how do I get a job which will generate the schema for each of our user databases, and write that out to some flat file somewhere?
Kindest Regards, Rod Connect with me on LinkedIn.
-
There isn't a built-in way to do it but you do have choices.
You can go with SMO which has a script method, but SMO has always been a little hard to swallow for me.
You can go with something like VSTE for DBAs. The MSBuild commandline has an option for scripting and that can be scripted and probably run from a job.
Or you can use my favorite method of Powershell. Powershell can script DBs very easily with very little effort.
I've actually made a video on this very topic. I don't really teach you how to turn it into a job, but it's a first step and I hope to complete another video showing you how to automate it very soon.
Anyway, watch it and see if it'll get you what you want because other than going with a 3rd party option, it's really your best bet.
http://midnightdba.itbookworm.com/PowershellScriptDBObjects/PowershellScriptDBObjects.html">
http://midnightdba.itbookworm.com/PowershellScriptDBObjects/PowershellScriptDBObjects.html
And while you're at it, I've got a Powershell basics video as well. It'll give you a quick overview of how PS works.
http://midnightdba.itbookworm.com/PowerShellOverview/PowerShellOverview.html">
http://midnightdba.itbookworm.com/PowerShellOverview/PowerShellOverview.html
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
"VSTE for DBAs". What is VSTE?
I'll review your PowerShell videos, thanks.
Kindest Regards, Rod Connect with me on LinkedIn.
-
VSTE -- Visual Studio Team Edition.
😎
-
Thank you. We don't have VSTE for anybody, let alone DBA's, so I guess I'll go with the PowerShell method.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Yeah, VSTE is expensive(5K'ish) and powershell is free.
I'll try to get on here tonight or tomorrow and shoot a video on turning it into a process for you.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Even I am affluent with the GUI only, however check the below article posted on sql server central that can help further on this.
Meanwhile we can also look into other avenues.
-Rajini
-
Yeah, when Yukon was released I gave a perfunctory effort to try to make that work, and lost interest pretty quickly. And while I haven't confirmed it in Katmai yet, my gut tells me they're going to tell you to go to powershell. I'm asking the question now and I'll let you know when I have an answer.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Here's the response I just got back from one of the devs at MS.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard">
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
This above link says, The wiki page 'Database Publishing Wizard
' does not exist.
- Manish
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply