Implementing and updating a SQL Server database database schema

Question

For my application, I have to support update scripts and the database may be affected.

I want to be able to upgrade an old version to the latest version without installing intermediate versions. For example. Suppose I have version A (oldest), B (intermediate), and C (new version). I want to be able to upgrade version A directly to version C. For application files, it's easy, I just replace the old with the new ones. However, for the database, I do not want to generate SQL Script to change the database schema from A directly to C, instead I want to first apply Script to change the schema from A to B and from B to C.

How to save database version for SQL Server database? Is there any special property that I can set, instead of embedding a version table? In my (.NET) code, I want to read the database version and, accordingly, execute the SQL update scripts in the correct order.

I will use SQL Server 2005 and SQL Server 2008.

Answer 1

I use advanced database properties, see Version Control and your database :

SELECT [value] 
    from ::fn_listextendedproperty (
        'MyApplication DB Version', 
        default, default, default, default, default, default);

...

EXEC sp_updateextendedproperty 
    @name = N'MyApplication DB Version', @value = '1.2';
GO

Answer 2

Attach the version table, its simple, effective for the past, erm, and for more than 10 years this has worked for me.

Answer 3

SQL Server.

, , , , .

, .

Answer 4

, . , , , , script.

So it doesn't matter what the current version of the database schema is. Scenarios will only make changes that have not yet been made to the schema.

Here you can find some stored procedures that significantly reduce the effort of writing upgrade scripts: How to maintain the version of the SQL Server database schema