Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Validate Deployed Databases by Version Expand / Collapse
Author
Message
Posted Friday, December 25, 2009 8:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 110, Visits: 718
Comments posted to this topic are about the item Validate Deployed Databases by Version
Post #839183
Posted Thursday, January 14, 2010 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 124, Visits: 1,634
Thanks for your article!

Is there a SQL 2000 version of this script?
Post #847508
Posted Thursday, January 14, 2010 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:58 PM
Points: 3, Visits: 21
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
Posted Thursday, January 14, 2010 11:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 110, Visits: 718
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.
Post #847788
Posted Thursday, January 14, 2010 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:58 PM
Points: 3, Visits: 21
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 »

Add to briefcase

Permissions Expand / Collapse