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 Thursday, April 14, 2005 1:28 PM
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 was thinking in terms of using DTS to transfer and manage all the files created by the scripting process.


-Isaiah
Post #174869
Posted Monday, April 18, 2005 6:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:21 AM
Points: 73, Visits: 69

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 (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
Post #175304
Posted Tuesday, April 19, 2005 3:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 10:48 AM
Points: 966, Visits: 933

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




Post #175872
Posted Friday, October 13, 2006 1:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:47 AM
Points: 142, Visits: 64

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
Post #315169
Posted Friday, October 13, 2006 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:42 PM
Points: 6, Visits: 181

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

Post #315228
Posted Friday, October 13, 2006 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2008 3:47 PM
Points: 1, Visits: 5
Any idea why Enterprise Manager generates extra blank lines when generating scripts? After several cycles of scripting and rebuilding a db, there is a lot of white space in the script.
Post #315323
Posted Friday, October 13, 2006 9:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 821, Visits: 2,029
You said "although it appears to be missing in SQL Server 2005 beta."  While that might be true it seems to be missing from the release version as well.  Could this be because it is not compatible with some of the new objects?

ATB

Charles Kincaid

Post #315327
Posted Wednesday, November 1, 2006 8:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:51 PM
Points: 6, Visits: 155
ApexSQL Script is the best for doing this type of this. Correct dependency order, scripting, custom tags + much more. Great product. Great support.

http://www.apexsql.com/sql_tools_script.asp

Rod
Post #319801
Posted Friday, November 17, 2006 3:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2007 3:35 PM
Points: 1, Visits: 1
I'm trying to use the sql 2000 version of scptxfr on sql 2005 however the script seems to keep running for days in databases where there are a lot of dependencies. Has anyone else experienced this or knows of a workaround?
Post #323753
Posted Tuesday, October 30, 2007 6:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:30 AM
Points: 41, Visits: 158
Is it me ?

I get all sorts of spurious errors if the command string is not all on one line.



Post #416469
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse