How to generate a sql script to update existing database and retain its data

Question

I have a SQL Server 2008 database (call it productionDB) that contains data and will be used in a production environment.

I have another SQL Server 2008 database (call it stagingDB) that is used in a staging environment.

The application that I am working on continues to evolve such that I am often making changes to the database schema (namely adding/editing tables and adding/editing stored procedures).

Up to this point, every time I have made changes to stagingDB I have generated scripts (via the SQL Server Scripts Wizard) that will drop/create all the schema. So, when I ran the scripts on the productionDB, it would successfully get updated to the updated schema, but all data that productionDB had would be lost - which has been fine, up til now.

Moving forward, I would like to generate scripts that will keep the existing data in productionDB as well as update its schema.

I cannot find an option(s) in the SQL Server Scripts Wizard that will do what I am describing.

Is the SQL Server Scripts Wizard capable of doing what I am looking for? If so, how?

Answer 1

Drop/recreate is a broadsword; you have to be more surgical. Here are some pseudocode examples:

Adding a new column:

if not exists (select your column from syscolumns)
   alter yourTable add
      yourColumn (type) NULL

If it cannot be nullable, set a default value, or update the column to populate data, then alter the table and set the column to be NOT NULL.

Renaming a column:

if not exists (select your column from syscolumns)
begin
   alter yourTable add yourColumn <type> NULL

   update yourTable set yourColumn = oldColumn

   alter yourTable drop oldColumn
end

Renaming a table:

if not exists (select your table from sysobjects)
begin
   create yourTable
       <your columns here>

   insert into yourTable ( <columns> ) select ( <columns> ) from oldTable

   delete from oldTable
   drop table oldTable
end

Notice a common theme; first, you must check to see if you've already performed this update, by checking for the existence of the updated schema elements. Then, add or drop only what's necessary, in the order add schema->migrate data->drop schema. It's a little more work to code, but it will run faster and save your data.

Answer 2

I'm not really aware of any way in SQL Server to automatically do what you want, but you may find commercial applications (Such as those supplied by companies like RedGate) that do what you want, but it is a very complex subject matter and there will always be scenarios that can't really be managed. I've always found a better scenario to script your modifications to Staging in such a way that they can be re-run against live. So you write scripts to amend the table structure - that way you maintain both the staging and live data when the scripts are run.

Answer 3

Your main problem will be that the SQL Server Script Wizard has no clue what state your database is in you will run the script against.

Thus, it does not know what differences to apply, e.g.: When to use alter table or when to use create table.

Yes, the script can add a If Not Exist clause, but that also doesn't know what to do when your table exists but needs an extra 2 columns. The SQL Server Script Wizard does not do a compare to another schema, it simply scripts your existing schema as is.

Depending on your version of SQL2008 you might have a version of BIDS installed along with SQL. In the start menu you should have your "Microsoft SQL Server 2008" folder, within that you have a link to "SQL Server Business Intelligence Development Studio" (BIDS).

Again, this will depend on the version of SQLServer2008. BIDS is the Visual Studio Development Environment.

Now, if you have that, depending on which version of BIDS you have (I think minnimum for schema compare tool is VS2008 SP1) you can create projects in there to compare 2 database schemas. The difference can then be scripted into a appropriate script. I don't know myself how to do it in VS2008 as we are using VS2010 with an edition which has it fully implemented. I looked at VS2008 but could not find it in an obvious location. According to Google though VS2008 SP1 has a schema compare tool.

If all else fails, you going to have to search for a free or pay-for Schema Compare Tool, such as SQL Compare 8.2.

The idea is the same, you have to compare 2 schemas with each other to see what is different. Then decide what you want to push through from staging to Live and script it accordingly. The tools merely do most of the manual work for you.