Validate Deployed Databases by Version

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by William Talada » Validate Deployed Databases by Version

Validate Deployed Databases by Version

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Talada

Posted Friday, December 25, 2009 8:22 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443

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

Post #839183

Robert.Sterbal

Posted Thursday, January 14, 2010 6:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 89, Visits: 1,330

Thanks for your article!

Is there a SQL 2000 version of this script?

Post #847508

aperregatturv

Posted Thursday, January 14, 2010 6:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 2:52 PM
Points: 3, Visits: 18

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

Post #847512

Bill Talada

Posted Thursday, January 14, 2010 11:31 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:31 AM
Points: 65, Visits: 443

aperregatturv (1/14/2010)

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.

Post #847788

aperregatturv

Posted Thursday, January 14, 2010 1:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 2:52 PM
Points: 3, Visits: 18

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.

Post #847897

« Prev Topic | Next Topic »

Permissions