Validate Deployed Databases by Version

  • Comments posted to this topic are about the item Validate Deployed Databases by Version

  • Thanks for your article!

    Is there a SQL 2000 version of this script?

  • Even thought the idea is ok but its not feasible in the production environment. we have hundreds of tables and views and stored procedures and we cannot include everything in std proc and think of maintaining that proc.

    We do it differently.

    We have a str proc called sp_DatabaseVersion and it returns the version of the database.

    CREATE PROCEDURE sp_DatabaseVersion

    @DBVersion int output,@DBType int output

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT @DBVersion = 1 -- Fresh start

    SELECT @DBType = 0

    END

    release 1 DBVersion = 1 and if you are deploying multiple database for different things use the dbtype to identify the database type.

    and for defaults we have sp_defaults that does all the default values to all the tables.

    and to update the defaults we use sp_UpdateDefaults which will update existing database defaults.

    this way we can track and we have atleast 5 different versions of the database and this str proc tells us which version they are running and we use a BuildInfo table when we update the database structure to update that table with values

    CREATE TABLE [dbo].[BuildInfo]

    (

    [ID] [int] NOT NULL IDENTITY(1, 1),

    [BuildNumber] [int] NOT NULL,

    [LastChanged] [datetime] NOT NULL CONSTRAINT [DF_BuildInfo_LastChanged] DEFAULT (getutcdate())

    ) ON [PRIMARY]

    which keeps track of when the build was updated.

    GO

  • aperregatturv (1/14/2010)


    Even thought the idea is ok but its not feasible in the production environment. we have hundreds of tables and views and stored procedures and we cannot include everything in std proc and think of maintaining that proc.

    You are correct that my proc does not make sense for an ever-changing production database. My proc is for vendors that are maintaining many customer's databases and want to know if the customer has dropped their objects or created others between vendor releases.

  • i would suggest 2 tools that we use can help maintaining the database structure.

    1. RedGate SQLCompare

    2. VS 2008 Database Edition

    these 2 allows you to compare and keep track database structural changes. you can even script the database and use that as master script and compare that with the database whenever there are changes made. it will show the changes really good. both tools that are life savers.

  • Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply