Link to home
Create AccountLog in
Avatar of jglazer63

asked on

Copy SQL Schema from Development to Live Server

What is the best way to copy just the schema (not the data) from a development SQL 2005 server to a  live SQL 2005 server asside from doing it by hand.  We make numerous changes to our development database and want to send those changes (including indexes) to the live server once the database and application has been tested.

The data on the live server needs to remain in tact.

Any suggestion?
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

In SSMS right-click database, choose Tasks | Generate Scripts
Avatar of jglazer63


Thats kinda what I was playing with but it seems to want to CREATE TABLE when on the live site the table already exists.  I need fields modified/truncated and new fields added along with indexes etc...
Avatar of Éric Moreau
Yea but wow is it expensive :-(
You can "re-do" the change (from the "old" actual schema that is in production to the "new" schema that is in deployment) and script that change.
For example, if the new schema has a new column, you should start from the original schema and add a column.
Then, rather than executing it in deployment, you can script it and execute in production.

The example (adding a column) is silly, but it can be applied to any change.

Well it's almost easier to just work through the gui column by column but its a pain in the butt :-)
>> but its a pain
I agree.

An alternative can be to create your new table into the production db with a different name and then copying the old content...
But it really depends on what the changes are...
Still pondering ... i actually did the create new table then copy content thing earlier.  Just seems that by now (this is not version 1.0) there would be a facility built in to help with this kinda stuff.
Hi jglazer63,

I remember I used this one some time ago. Give it a try:
I'm not sure what you're development environment is like, but you might consider using a cumulative script for the changes.  Each developer runs the sql for a change on a development database and then adds that sql to the cumulative script.  This has a few advantages: development can be refreshed from production if needed, you can test the changes before applying to production and you are sure exactly what will be applied to production, and you have a history of changes being applied to production.  This approach works fine manually or using SSMS to generate each change script, or with most database modeling tools.
Avatar of jogos
Flag of Belgium image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I do have a full version of vs pro but I've never had any luck doing what you suggest.  Maybe I haven't spent enough time figuring it out.
Yes using a db-project from start to end, including an upgrade-script is a large switch in how to look at your db-work.
But the db-compare functionality for the upgrade-script can be done on 2 databases without implementen any source-control.
This is the way to go.