Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to Schedule a SQL Server Database Creation Script Expand / Collapse
Author
Message
Posted Friday, April 08, 2005 1:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp


Jon
Post #173486
Posted Thursday, April 14, 2005 4:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 20, 2007 4:52 AM
Points: 13, Visits: 2

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. 

 

 

Post #174666
Posted Thursday, April 14, 2005 4:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 20, 2007 4:52 AM
Points: 13, Visits: 2
Take back my comments, as View have permissions set but Tables and Stored Procedures don't
Post #174667
Posted Thursday, April 14, 2005 5:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Monday, August 19, 2013 12:07 PM
Points: 1,929, Visits: 228
Wow, learn something new everyday. Great article.

Brian Knight
Free SQL Server Training Webinars
Post #174682
Posted Thursday, April 14, 2005 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2008 1:57 AM
Points: 3, Visits: 2

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

Post #174688
Posted Thursday, April 14, 2005 6:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 04, 2007 7:42 AM
Points: 20, Visits: 1
Good Article


Post #174701
Posted Thursday, April 14, 2005 8:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61

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
Post #174764
Posted Thursday, April 14, 2005 9:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:53 AM
Points: 226, Visits: 688

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.




Post #174815
Posted Thursday, April 14, 2005 11:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 13, 2005 10:53 AM
Points: 98, Visits: 1

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




-Isaiah
Post #174837
Posted Thursday, April 14, 2005 11:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:53 AM
Points: 226, Visits: 688
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.


Post #174843
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse