Not too sure if this would be a good and proper way to go about things, so I apologize if I'm kind of far off.

Currently running Microsoft SQL Server 2005, and in this we have a database for a production website, and now just made a copy of it for a development version of the website. What my question is, what would be the proper way to "replicate" or "mirror" table structure changes and things of that nature from the development database to the production one, when we do our upgrade? Is there a simple way to do a migration like this?


2 Answers 2


If you have access to a db version control system (ala Red Gate), you can have it create a deployment script fairly easily.
If not, just create a file and script any ddl changes you make and store them in a file, then when you are ready (and have backed up prod) you can run the script against prod to make your changes.
Be aware that certain changes can require extra steps (adding a not null column without a default is always a favorite), but that is the basic procedure. Also be aware of how your data might affect your ability to promote changes (key constraints, etc.) which is why tools like Red Gate's SQL Compare and Version control are worth the money.

No I don't work for Red Gate, but I've had to do this job with and without their tools and it is a lot more work without them.

  • Something like Red Gate looks very promising, just I don't see us spending the money to grab it. Looks like I'm going to just have to script out the changes I need. Thanks though!
    – Chiggins
    Commented May 21, 2013 at 15:54

Is really a replication what you need? it is common practive to periodically restore from production environment databases to other non-productive envs. This of course can be done in many ways but is tightly related to you development model in the company (or the testing path you have to use for your applications). If you have currently have the app_v1.0 in production and you receive app_v2.0 you actually restore (you might read duplicate) you productive DB into another environment, upgrade the app, perform all needed tests and than decide to upgrade the prod. If you have a website it is supposed you will track all DDL/DML performed to upgrade your db schema to support the new website and then deploy them in prod during the release. Data replication is not here the solution so. Probably you want to have a software able to summary and keep schemas changes to later apply them in production but of course this is not done by replication. You might use a data modeling software or other more sophisticated software like the one from RedGate.

  • 1
    No, I don't think they meant replication explicitly. I think they just meant replaying the changes to the schema. Commented May 20, 2013 at 20:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.