Service Release 1 of Visual Studio 2005 Team Edition for DB Pros (aka datdude) includes a new feature that hasn't been mentioned anywhere by the datadude team.
Any database deployed using SR1 will contain an extended property called 'microsoft_database_tools_deploystamp'. You can see it here look:
The deployment script that is output from datadude now contains the following code:
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
IF ((SELECT CAST(value AS nvarchar(128))
FROM
[$(DatabaseName)]..fn_listextendedproperty('microsoft_database_tools_deploystamp', null, null, null, null, null, null ))
= CAST(N'a5f48648-7027-45cb-929c-fa06d797290d' AS nvarchar(128)))
BEGIN
RAISERROR(N'Deployment has been skipped because the script has already been deployed to the target server.', 16 ,100) WITH NOWAIT
RETURN
END
END
GO
at the very end of the script we get this:
USE [$(DatabaseName)]
IF ((SELECT COUNT(*)
FROM
::fn_listextendedproperty( 'microsoft_database_tools_deploystamp', null, null, null, null, null, null ))
> 0)
BEGIN
EXEC [dbo].sp_dropextendedproperty 'microsoft_database_tools_deploystamp'
END
EXEC [dbo].sp_addextendedproperty 'microsoft_database_tools_deploystamp', N'a5f48648-7027-45cb-929c-fa06d797290d'
GO
I like this subtle touch, its a nice use of the oft overlooked extended properties for tracking which version of the script has been deployed. Obviously the deploy stamp changes on each build.
-Jamie