October 3, 2007 at 4:23 am
Hey,
We are currently looking at how easily we can start to use scripting methods to efficently take across the tables that we create on our test databases across to the live ones.
Basically the way we do things now is not efficent and we would like to find a way in which we can do things properly saving us time.
We are using Sql Server 2000.
I have looked into the automatesd generate script from the neterprise manager when i right click, but how effieicent is this, do i dont lose default values which i have set. For example if i have a field with a default value of 0 i want to make sure this isnt lost when i script it over onto the live server.
Also if i am copying across new fields to an existing table do i just write the basic script of
ALTER TABLE [OWNER].[TABLE_NAME] WITH NOCHECK ADD
CONSTRAINT [DEFAULT_CONTRAINT_NAME_Has_to_be_unique] DEFAULT (DEFAULT_VALUE_IE_0_OR_GETDATE) FOR [TABLE_FIELD_NAME]
ALTER TABLE [OWNER].[TABLE_NAME] WITH NOCHECK ADD
CONSTRAINT [CONTRAINT_1] DEFAULT (DEFAULT_VALUE_0) FOR [FIELD_1],
CONSTRAINT [CONTRAINT_2] DEFAULT (DEFAULT_VALUE_GETDATE) FOR [FIELD_2]
Is there a better cleanner way?
Many Thanks in Advance
Zal
October 3, 2007 at 5:09 am
Well, did you try it? There are lots of options you can set when creating the script from EM - you can include indexes, triggers, defaults, constraints and other things. You can even script all dependent objects (but I never used this feature, so I can't say anything about it).
BTW, when speaking about development, I prefer to create any objects in development by writing a script, so later when moving it to production, I can just run the same script, and don't have to create it.
October 4, 2007 at 12:23 am
I'm amazed that no-one has mentioned this so far, given that they are the sponsor/owner of this site!
Take a look at SQLCompare from RedGate.
This has some very nice features, including the ability to save the change scripts. This would allow you to create a change script to go from DEV to TEST, and then re-use that script for implementation to Production.
October 4, 2007 at 1:12 am
SQLCompare is indeed great for this job.
We use it to update the database of our customers.
After every release we make a snapshot of our development database.
When we made changes and want to deliver a new version to the customer, we compare to the current database with the last snapshot and it produces nice script with all the changes.
In your case with a new database, you compare your database with an empty one and get the scripts to deploy your database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply