how-to manage production deployment for sql server 2005 database upgrade with <10 tables and <20 stored procedures

Question

I have a Sql Server 2005 Standard production database with some <120 tables and <150 stored procedures. The newer version update requires beside the application layer upgrade also the upgrade to the db. According to the resources provided I intent to perform the upgrade as follows:

• create for each new table create script with insert data ( if any) . Please the script in separate file named: n.Create_Table_Name.sql
• create for each new stored procedure create script with permissions. Please script for each proc in n.Create_StoredProcName.sql
• create for each alter proc new file named n.Alter_StoredProcName.sql
• Run each script one by one to the TEST db. If test failed ( something has to be changed , etc. , restore back full_backup on top of test db and start all over.

I have a DDL trigger which stores every DDL event in a table - e.g. I could easily track what everything has changed from a particular time point .

Ary there any scripting tools other than SSMS I could simply pass the names of the objects which will generate all the ddl statements for me + the permissions I COULD RELY ON ?

The whole process is kind of slow, but has worked several times, yet I wander if there are any improvements to be made ( No other software than SSMS could be install on our PROD servers )

Answer 1

You can create a PowerShell script that will use SMO and do the job for you. You can rely on it as soon as SSMS is actually based on SMO.

Answer 2

Here is the cmd for running the sql files

Ended up using ScriptDb.exe or you could use SSMS . On windows 7 and sql server 2008 did not build correctly at the beginning. Had to find the appropriate dll's to build it first ...

ScriptDb.exe -con:server=(local);trusted_connection=yes -outDir:. -ScriptAllDatabases -Permissions -Purge

and than copying just the files of the needed objects: SELECT distinct

  'COPY D:\Temp\Temp\DBNAME\Programmability\StoredProcedures\' + 
  ObjectName + '.sql D:\temp\temp\DBNAME\1.2.8\StoredProcedures\'
  + ObjectName + '.sql'

FROM tbDataMeta_ChangeLog
where ObjectType = 'PROCEDURE' 
AND EventDate>'2010-02-10'






USE [MY_DB]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = OBJECT_ID(N'[DF_EventsLog_EventDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[DbObjectsChangeStore] DROP CONSTRAINT [DF_EventsLog_EventDate]
END

GO

USE [MY_DB]
GO

/****** Object:  Table [dbo].[DbObjectsChangeStore]    
Script Date: 02/11/2010 17:58:00 ******/
IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[DbObjectsChangeStore]') AND type in (N'U'))
DROP TABLE [dbo].[DbObjectsChangeStore]
GO

USE [MY_DB]
GO

/****** Object:  Table [dbo].[DbObjectsChangeStore]    
Script Date: 02/11/2010 17:58:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DbObjectsChangeStore](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [varchar](256) NOT NULL,
    [EventType] [varchar](50) NOT NULL,
    [ObjectName] [varchar](256) NOT NULL,
    [ObjectType] [varchar](25) NOT NULL,
    [SqlCommand] [varchar](max) NOT NULL,
    [EventDate] [datetime] NOT NULL,
    [LoginName] [varchar](256) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DbObjectsChangeStore] 
ADD  CONSTRAINT [DF_EventsLog_EventDate]  
DEFAULT (getdate()) FOR [EventDate]
GO


IF  EXISTS (SELECT * FROM sys.triggers
 WHERE parent_class_desc = 'DATABASE' AND name = N'trig_BackUpDbObjects')
DISABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE

GO

USE [MY_DB]
GO

/****** Object:  DdlTrigger [trig_BackUpDbObjects]   
 Script Date: 02/11/2010 17:58:49 ******/
IF  EXISTS (SELECT * FROM sys.triggers 
WHERE parent_class_desc = 'DATABASE' 
AND name = N'trig_BackUpDbObjects')
DROP TRIGGER [trig_BackUpDbObjects] ON DATABASE
GO

USE [MY_DB]
GO

/****** Object:  DdlTrigger [trig_BackUpDbObjects]    Script Date: 02/11/2010 17:58:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create trigger [trig_BackUpDbObjects]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as

set nocount on

declare @data xml
set @data = EVENTDATA()

insert into [dbo].[DbObjectsChangeStore](databasename, eventtype, 
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)




GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE
GO