Validate Deployed Databases by Version

Question

Post reply

Validate Deployed Databases by Version

Bill Talada

SSChampion

Points: 11954
More actions
December 25, 2009 at 8:22 pm

#218982

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

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

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

Robert.Sterbal SSCrazy Points: 2855 More actions · Answer 1

Thanks for your article!

Is there a SQL 2000 version of this script?

aperregatturv Grasshopper Points: 19 More actions · Answer 2

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

Bill Talada SSChampion Points: 11954 More actions · Answer 3

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.

aperregatturv Grasshopper Points: 19 More actions · Answer 4

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 19, 2016 at 1:25 pm

#1880367

Thanks for the script.