My client having the SQL server 2008 cloud version as test server where, we deployed the sql database of my application.This deployment done before 7 month. Since while testing application during these 7 months, client added lot of data. But according to new requirements we added number of tables and columns in existing tables at our end. Now I want to update Client test server database but his data should not affect. What is the right way to backup and restore these updates from development server to client's test server. we are having sql server 2008 R2 as development server.
You should have a plan. For example we do it like this:
1. Compare 2 databases with some compare tool(we use RedGate)
2. Generate migration scripts for structure that will update database structure
3. Generate migration scripts for data that will update new columns and will fill classification tables etc.
4. Take full backup before applying migration scripts
5. Run migration scripts
6. Update App version on clients if necessary.