Write an SQL update script so it always updates the db correctly, regardless of the db version our client has

Our (ASP.NET) software uses a MSSQL database. We periodically have new releases of our software which sometimes include changes to our database.

Currently we write an SQL update script for each release that updates the database, based on the previous version and the new version. This gets annoying when we have to update an older client, have to apply each SQL update script in order until it's up to date again. I'm trying to find a way not to have to do this开发者_运维问答, that I can have just 1 SQL script that will always update the database to the new version, no matter what version the old database is (or even if it doesn't exist).

The only way I can think of doing this is to have massive amounts of 'IF NOT EXISTS' for each table and column and stored procedure. Is there a better way of doing this (without losing the data in the database)?

P.S. I'm finding it difficult to google for this (not sure how to describe it), hence this question.

I think it would make sense to store a schema update version in the database. The update script would read the current version and based on that, have it execute all of the previous update scripts in order until it is current. This would be a going-forward approach, since your old schema versions would not have the version number stored. If you could identify a distinguishing characteristic to each prior schema update - then it could still work. So, your update logic would have to be in a script and your release package would have to ship with all previous update sql files.

What I have done is maintain a dev copy of each database version, as it exists for a particular version, and use the redgate tools to generate the scripts I need. If I need a script to upgrade from V1.3 to V2.5, I pick those two databases and the scripts get generated.

You might be able to do it all in a single script, but you'll need to do it by hand. Why not use a tool like redgate for this and make your life simple?

http://www.red-gate.com/products/sql-development/sql-compare/

Although making each DDL script 'rerunnable' with IF NOT EXISTS etc is good practice, I can see how that approach will eventually get messy and cumbersome. It can also get especially thorny when you are running data conversion scripts.

The approach I have always taken is to number each script sequentially and store them somewhere and keep track of the ID of last run script; that ID is your DBVersion. The DBVersion number is stored in a settings or version table in the DB.

All that's left is to have some process for comparing the DBVersion value with the MAX(id) of your scripts table (or file, or dictionary or whatever)

CREATE TABLE DBVer
(
    DBVer INT PRIMARY KEY CLUSTERED 
) 

INSERT INTO DBVer(DBVer)
SELECT 1

CREATE TABLE DBScripts
(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Script NVARCHAR(MAX)
)

CREATE PROC UpdateDB
AS 

DECLARE @i    INT,
        @max  INT,
        @SQL  NVARCHAR(MAX)

SELECT @max = MAX(ID), @i = MAX(DBVER)
FROM DBScripts 
CROSS JOIN DBVer --only ever one row in here but make sure!

if @i>@max
    RETURN --no updates, let exit early

WHILE @i <= @max --loop through and apply new scripts
BEGIN
    SELECT @SQL = Script
    FROM DBScripts
    WHERE ID = @i

    EXEC sp_executesql @statement = @SQL
    SET @i = @i + 1
END

UPDATE DBVer SET DBVer = @i --this can also be in the loop 
GO

This is not a total solution but you get the general idea.

Things to think about include where the update is getting called from if each of 100 clients call updateDB on startup it may be worth putting in some locking or some other means to manage a potential race condition. It may also be worth wrapping everything in a transaction. DDL can be rolled back but you can also fill up the log very quickly with big changes.

You could use SQL Compare by Red Gate to generate ONE diff script between each version of your database, i.e.

v1 to v2 diff script
v1 to v3 diff script
v1 to v4 diff script
v2 to v3
v2 to v4
v3 to v4

This would cover all changes, including code and table changes - it could also include data changes if you have "static" values that you update as an upgrade - you would need their sql data compare product for that.