Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Schedule a SQL Server Database Creation Script


How to Schedule a SQL Server Database Creation Script

Author
Message
iadams
iadams
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1
I was thinking in terms of using DTS to transfer and manage all the files created by the scripting process.


-Isaiah
Malcolm Leach
Malcolm Leach
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 71

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
ChrisMoix-87856
ChrisMoix-87856
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 941

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
Jonr
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 65

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
Jed S Wilson
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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


Simon Shutter
Simon Shutter
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 2383
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?

ATBCharles Kincaid
Rod Weir
Rod Weir
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 160
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
mark blakey-271739
mark blakey-271739
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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?
ourdai
ourdai
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 158
Is it me ?

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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search