Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a web app that is currently live. I had the need to make major modifications to the database and now I need to replace the current one.

My dev SQL instance is not the live one. I usually just make a backup of the new DB, blow the old one away and Restore my updated one. But now I have data I need to preserve.

Most of the current tables have changed, in that extra columns have been added, all existing columns are still there and unchanged.

I have access to Management Studio.

What is the right way to do this?

share|improve this question
    
Your live web app - are all of it's references to the DB server and the DB it's self hard coded in code, or using a config value? –  Dave Holland Mar 13 '10 at 17:35
    
Uses LINQ to SQL, so its just in webconfig. –  Blankasaurus Mar 14 '10 at 2:48
add comment

1 Answer

up vote 1 down vote accepted

The "right way" to do this is to treat any changes to your database during development as only temporary and compile a list of database build scripts so that you can rebuild your new schema on your production database. Any changes that you make should be stored in a SQL upgrade script tied to the database version (1.0, 1.1, 1.2...) or better yet put into a source code repository for version control along side your software code. When you want to update your production database you would then run the sequence of update scripts to upgrade the database to the latest version.

You can sometimes generate the change scripts after working on your dev database in Management Studio by right clicking on a table or stored procedure and selecting "Script To". This will generate an alter or create statement that can be used to build your update scripts. Once you have all the update scripts finished, copy your production database to your dev box and test the upgrade to make sure everything works. Then when you are ready to update the live system, create a backup and then run the scripts on the production server.

The alternative is to generate a blank database with the correct schema and then copy all you data into the blank database. For a complex schema this is usually difficult to do and the process can require significant downtime, where as using update scripts can often be done with minimal downtime.

For more information see this question about managing database versions and upgrades.

share|improve this answer
    
For the alternative, how do you migrate the data? Isn't there some kind of transform thing in management studio? Only 2 tables have data that changes based on the user, everything else is there for dropdown lists and categories etc... –  Blankasaurus Mar 14 '10 at 2:51
    
The "right way" would be to use SSIS (msdn.microsoft.com/en-us/library/ms140052.aspx) but there are other ways too: stackoverflow.com/questions/20363/… Note however that today it might be 2 tables, but that could easily grow into a larger number of tables in future releases. Using database versioning is a much better long term solution. –  Greg Bray Mar 14 '10 at 20:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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