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?
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.