How to Schedule a SQL Server Database Creation Script
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp
Jon -
Great article, makes the creation on scripts much easier.
The only thing that is missing from the process is the permissions for each object they are not created, and as a result I have to create these manually.
-
Take back my comments, as View have permissions set but Tables and Stored Procedures don't
-
Wow, learn something new everyday. Great article.
Brian Knight
Free SQL Server Training Webinars -
This only works for simple databases as it does not take account of dependencies between objects and can create objects in the wrong order.
-
Good Article
-
Thanks for the comments guys, useful feedback like this is always much appreciated.
Unfortunately, yes, scripting of the table & stored proc security permissions does seem to be a limitation of this method, and was something I spotted only after submitting this article. It did make me wonder why it had not been included by MS - I'd have expected to see them applied after each object definition, or perhaps in a separate file.
Other than manually scripting them I'm afraid I can't offer a reasonable way around this at present.
With regard to the object order, probably the best way to deal with that is to do it with a number of passes, and not drop the objects which have already been created between each run of the script.
It's messy as you'll get lots of 'object already exists' errors, but at least it does eventually solve the creation order dependency problem.
How MS get around this in the upgrade scripts is something of a mystery, but it might explain why they take so long to run.
Jon -
Intersting article, and actually, I just finished one similar tool with a set of different features.
I have been facing migrating database objects (several hundered sometimes)from tens of databases in different environments to some other environments.
I will probably send my article to Steve sometime next weekend to share with all others.
-
I guess you can use DTS to create a central repository for all these scripts. Great article, Thanks!
-Isaiah -
DTS is not a good answer in terms of convenience and team cooperation. Yes, DTS has a task call "Copy SQL Server Objects" that can generate most of db objects' scripts, but I do not like it at all, I will answer it in my article in future.
-
I was thinking in terms of using DTS to transfer and manage all the files created by the scripting process.
-Isaiah -
I guess the issue here is 'you get what you pay for' - this is a great article and provides an excellent (and free!) way to version your databases. You could even do a little more scripting and push the scripts into a SourceSafe project incrementally every night. i.e check everything out and then overlay the scripts on disk with the newly scripted DB. Then check it all back in, add and new scripts and delete any that are now missing and you have an audit trail of what changed on your development/test/production database that day.
However, this won't tell you who changed things and, crucially, why.
For a full change management process for SQL Server code I recommend you take a look at DB Ghost (http://www.dbghost.com). It can compare databases, build databases from drop/create scripts under source control (with no dependency issues) and can script out entire databases (including permissions!). All this can be run from the command line as well as the UI to acheive a fully automated process.
Having the developers modify the drop/create scripts in source control means that the DB Ghost Process is perfectly aligned with configuration management best principles (who changed what, why and when) and is therefore also a huge step towards Sarbanes Oxley / HIPAA / BASEL II compliance etc.
Once you've used it you'll wonder why no one thought of it before.
- Malcolm
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com -
One thing I have noticed with SCPTXFR is that it changes the name of most of my Primary Keys. Example:
Source database PK Index: PK_tblBox_BoxID
Dest database PK index: PK__tblBox__1A69E950
I'd like to retain the descriptive names. Does anyone know a way to accomplish this?
Chris
-
I don't unfortunately Chris. However, from memory, I think it's replacing the name of the primary key with its objectid. Quite why MS did this I don't know.
Jon.
Jon -
Thank you for the article; very helpful.
Also a minor discrepancy: In the introduction to Real World Examples it states "NB: All examples use a database server called DEVSVR." but some examples use svdb3
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply