Detecting if a database has been updated

[steve_rm]

Hello,

I have a database that will need to be modified, i.e. adding columns, stored procedures, etc.

I can't create a backup as the database is live and will be continue to be updated with customer data.

My colleges what me to create the updates in code which I have done. However, my real problem is how do I know if the database has been updated and is current version.

I will not be updating the data that has been entered, only doing things like:
Alter [tableName] add Salary money null
create procedure dbo.insertPayment .....


example

if(currentVersion) then
'No need to update
Else
'Updates are available
'Add column
'Drop column
'create procedure
'etc

Is there any method that I could use to detect if the database is current or not.

I hope I have been clear on this.

Using VS 2005 and SQL Server 2005

Many thanks in advance,

Steve

[kaffenils]

You can do that in several ways.

We are using a 3rd party tool from Innovartis called DBGhost (http://www.dbghost.com) . That is an excellent tool for keeping track of changes and comparing and building changes on a development system to a live system. I can't live without it anymore.

If you don't want to spend any money you can always use DDL triggers in SQL Server 2005 and track DDL changes.

My experience tells me that it will be cheaper to pay the $350 a DBGhost license cost than to use the few manhours you get for $350 to develop something that will likely not work.

If you need to deploy changes to any customers then Innovartis also have excellent deployment tools for database upgrading.

For the record, I am not working for Innovartis, I just happen to love their products

[szlamany]

We save all our ALTER TABLE and CREATE PROCEDURE scripts as .SQL files.

We save them in VISUAL SOURCE SAFE.

It's effortless to keep track, by date, of what's changed.

All we ever have to do is execute the .SQL script in QA or with OSQL/ISQL (we actually have a VB program that creates .BAT file of these - based on date changed).

All our SPROC scripts look like this:

USE Stufiles
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[frmAddDrop_View]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[frmAddDrop_View]
GO
CREATE PROCEDURE frmAddDrop_View
     @PassConnId int
    ,@RetStat int Output
    ,@RetText varchar(100) Output
    ,@RetMode int Output
    ,@RetGrid int Output
    ,@RetExtra varchar(100) Output
    ,@PassMode int
    ,@PassExtra varchar(10)
    ,@PassTest varchar(10) 
    ,@Yr int
    ,@Bldg int
    ,@Date datetime
    ,@Class varchar(100)
    ,@Crs varchar(4)
    ,@Sectn int
AS
 
Set @RetStat=0
Set @RetText=''
Set @RetMode=0
Set @RetGrid=0
Set @RetExtra=''
 
.
.
.
Go
GRANT EXECUTE ON frmAddDrop_View TO StufilesUser
Go
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[kaffenils]

Szlamany, have you tried DBGhost? With it you don't need to keep track of all the ALTER scripts. We use Subversion and store the CREATE script generated by DBGhost.

When we deploy the scrips to an existing database DBGhost will compare the CREATE scrips with the actual database and create the necessary alter scripts.

You can even use the DBGhost packager and create an exe or C# project with the CREATE scripts (no, not the ALTER scripts ) that you can send to your customers. When they run the exe DBGhost will compare the scripts to their database and do the necessary modifications.

[szlamany]

No I've not tried that...

I will store it in the back of mind for when our current methods become too cumbersome...

Thanks for the info!

[steve_rm]

Hello,

Thanks for your ideas. I was really looking to purchase a lience for DBGhost, I was looking more of a cost affective way to do this comparison. The company won't pay for a full lience. They don't think I am worth it.

My only idea and not sure what you guys think about this is to have another table called UpdatedDatabase, and in there have 2 columns (DB_Version, Date Updated). Each time the database is updated it will increment the DB_Version and insert the date updated. However, if you look at my code above in the if statement i am left wondering what to compare this DB_Version with.

I was thinking about using the my.settings and entering a value called IsUpdated so I could use my.settings.Isupdated value to compare with the DB_Version. But still not sure if this will work.

Any ideas on this cost effective solution about comparsions, as I only want the code to run when there as been sometime to update.

Thanks in advance,

Steve

[kaffenils]

You could of course create a "version" table on the destination databases and. The version table contains one column and one row telling you what the last script was that you ran from the UpdateScripts table.

On the source/development database you create a table, UpdateScripts, with two columms, version (int identity) and DDL_script nvarchar(max). You then create DDL triggers that writes the DDL scripts automatically to the UpdateScripts table.

Then when you want to upgrade any of your production servers with modifications done on the source server you simply run all scripts with higher version numbers than what is currently in the Version table on the destination.

There area a lot of pitfalls, e.g. if you change a nullable column to not null. That would require you to first update the column with not null values before you set the NOT NULL property. You will then have to add the UPDATE script manually to the UpdateScripts, otherwise running the NOT NULL script on the destination will fail.

What kind of company can't afford a $350 license for such a valueable tool like DBGhost? I recommend that you donwload the 30 day trial and show them what it is good for. Just ask if you need help.

[GaryMazzone]

If we have multiple sites using an application and we need to check the multiple sites using DBGhost would we need to buy a license for each site the app is running at? We currently have the application at 10 remote clients.

[kaffenils]

If we have multiple sites using an application and we need to check the multiple sites using DBGhost would we need to buy a license for each site the app is running at? We currently have the application at 10 remote clients.

The DBGhost Professional Edition license is a personal license for one user. That means that you can use it to sync as many databases to as many servers as you want.

If you want to distribute an exe file to your customers so that they can upgrade their existing databases to newest version regardles of what version they have, you need DBGhost Packager Plus, quite expensive ($1998), but worth the cost if you have a lot of customers and databases in different versions. There is a cheaper version of DBGhost ($295) that only adds schema changes (alter scripts) in the exe file. You would then need some kind of versioning system to make sure that alter scripts are run in correct order between versions.

The DBGhost Packager is also a personal license that you can use to create as many installations files as you want and distribute to who you want.

[szlamany]

... a lot of pitfalls, e.g. if you change a nullable column to not null. That would require you to first update the column with not null values before you set the NOT NULL property. You will then have to add the UPDATE script manually to the UpdateScripts, otherwise running the NOT NULL script on the destination will fail.

We always consider stuff like the NULL issue when scripting a table change...

Here's a script that inserts a column at the second-to-last position in a table - moving the right most column (and it's data) over one...

EXEC sp_rename 'Course_T.TDate','OldTDate','COLUMN'
Go
Alter Table Course_T Add Tdate datetime Null
Go
Update Course_T Set TDate=OldTDate
Update Course_T Set OldTdate=Null
Go
EXEC sp_rename 'Course_T.OldTDate','Perkins','COLUMN'
Go
Update Course_T Set Perkins=Null
Alter Table Course_T Alter Column Perkins varchar(3) Null
Go

We like our TDATE columns to always be the right-most column in all our tables...