How to Schedule a SQL Server Database Creation Script

  • Jonr

    SSCrazy

    Points: 2566

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp


    Jon

  • b_avery@yahoo.com

    SSC Enthusiast

    Points: 167

    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. 

     

     

  • b_avery@yahoo.com

    SSC Enthusiast

    Points: 167

    Take back my comments, as View have permissions set but Tables and Stored Procedures don't

  • Brian Knight

    One Orange Chip

    Points: 27249

    Wow, learn something new everyday. Great article.

  • Robin Coode

    SSC Rookie

    Points: 37

    This only works for simple databases as it does not take account of dependencies between objects and can create objects in the wrong order.

  • ashishnaik1

    SSC Veteran

    Points: 252

    Good Article

  • Jonr

    SSCrazy

    Points: 2566

    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

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

    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.

  • iadams

    SSCommitted

    Points: 1890

    I guess you can use DTS to create a central repository for all these scripts.  Great article, Thanks!


    -Isaiah

  • jeffrey yao

    SSCarpal Tunnel

    Points: 4244

    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.

  • iadams

    SSCommitted

    Points: 1890

    I was thinking in terms of using DTS to transfer and manage all the files created by the scripting process.


    -Isaiah

  • Malcolm Leach

    SSCommitted

    Points: 1517

    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

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    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

  • Jonr

    SSCrazy

    Points: 2566

    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

  • Jed S Wilson

    Grasshopper

    Points: 24

    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 26 total)

You must be logged in to reply to this topic. Login to reply