Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aaronexodus
Starting Member
1 Post |
 Posted - 2006-05-08 : 15:26:25
|
| I have written some software using .NET 2.0, a WinForms app, which uses SQL Server Express as a database. Although I've done my best to set up the database to take into account all of the features, etc., after releasing the first version, I need to make some changes to the database schema, but want to do so without wiping out the entire database for existing users. How do I go about upgrading the database schema for users who are already using the software? I'm thinking I should start by writing a helper app to do so whose sole purpose is to upgrade the schema. The app would be run at install-time, but I don't have any idea how to actually upgrade the schema. I am using C++/CLR .NET, but examples in other .NET languages are more than welcome. Any help is greatly appreciated. Thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-08 : 17:18:41
|
| We generate scripts for each "version" that we release which take care of changing the Database Schema from Version 1 to Version 2.We generate "change scripts" for each change we make to the schema, and save them to sequentially numbered files. In each of these we put an "INSERT INTO VersionLog ..." statement that records that that version was run (and the date etc.). So we know a) which version the client is on and b) the date/time when each upgrade was run.These scripts contain statements that are generally either Simple of Complex:Simple: ALTER TABLE foo ADD COLUMN bar VARCHAR(10)Just add a column to the end of the schema for a given table, all existing rows are NULL in this column.Complex: Create new temporary table with revised/additional column; Copy all data from original table to temporary table; Drop original table and rename temporary table; Reinstate PKs, Indexes, FKs, etc.Kristen |
 |
|
|
|
|
|
|
|
How do I upgrade an existing database schema?