If you actually look at the script first, GOOD JOB !!! Scan for the string "--script edit required" to find some spots that need a little more info Take the time to understand the code in these spots so you can provide the correct information. /* Microsoft SQL Server - Scripting */ /* Server: ????? */ /* Database: admin */ /* Creation Date 11/4/98 1:05:34 PM */ /****** Object: Stored Procedure dbo.dbSync Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.dbSync') and sysstat & 0xf = 4) drop procedure dbo.dbSync GO /****** Object: Stored Procedure dbo.logSync Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.logSync') and sysstat & 0xf = 4) drop procedure dbo.logSync GO /****** Object: Stored Procedure dbo.maintenance Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.maintenance') and sysstat & 0xf = 4) drop procedure dbo.maintenance GO /****** Object: Stored Procedure dbo.check_ident Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.check_ident') and sysstat & 0xf = 4) drop procedure dbo.check_ident GO /****** Object: Stored Procedure dbo.check_outfiles Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.check_outfiles') and sysstat & 0xf = 4) drop procedure dbo.check_outfiles GO /****** Object: Stored Procedure dbo.CheckPULIST Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.CheckPULIST') and sysstat & 0xf = 4) drop procedure dbo.CheckPULIST GO /****** Object: Stored Procedure dbo.convert_share_to_drive Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.convert_share_to_drive') and sysstat & 0xf = 4) drop procedure dbo.convert_share_to_drive GO /****** Object: Stored Procedure dbo.expunge_users Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.expunge_users') and sysstat & 0xf = 4) drop procedure dbo.expunge_users GO /****** Object: Stored Procedure dbo.run_script_remote Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.run_script_remote') and sysstat & 0xf = 4) drop procedure dbo.run_script_remote GO /****** Object: Stored Procedure dbo.verify_sysusages Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.verify_sysusages') and sysstat & 0xf = 4) drop procedure dbo.verify_sysusages GO /****** Object: Stored Procedure dbo.dbSync_driver Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.dbSync_driver') and sysstat & 0xf = 4) drop procedure dbo.dbSync_driver GO /****** Object: Stored Procedure dbo.delete_archive Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.delete_archive') and sysstat & 0xf = 4) drop procedure dbo.delete_archive GO /****** Object: Stored Procedure dbo.dumpDatabase Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.dumpDatabase') and sysstat & 0xf = 4) drop procedure dbo.dumpDatabase GO /****** Object: Stored Procedure dbo.free_semaphore Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.free_semaphore') and sysstat & 0xf = 4) drop procedure dbo.free_semaphore GO /****** Object: Stored Procedure dbo.help_users_all Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.help_users_all') and sysstat & 0xf = 4) drop procedure dbo.help_users_all GO /****** Object: Stored Procedure dbo.hold_semaphore Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.hold_semaphore') and sysstat & 0xf = 4) drop procedure dbo.hold_semaphore GO /****** Object: Stored Procedure dbo.logSync_driver Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.logSync_driver') and sysstat & 0xf = 4) drop procedure dbo.logSync_driver GO /****** Object: Stored Procedure dbo.maintenance_driver Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.maintenance_driver') and sysstat & 0xf = 4) drop procedure dbo.maintenance_driver GO /****** Object: Stored Procedure dbo.release_semaphore Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.release_semaphore') and sysstat & 0xf = 4) drop procedure dbo.release_semaphore GO /****** Object: Stored Procedure dbo.reset_subsystem Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.reset_subsystem') and sysstat & 0xf = 4) drop procedure dbo.reset_subsystem GO /****** Object: Stored Procedure dbo.setLogDump Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.setLogDump') and sysstat & 0xf = 4) drop procedure dbo.setLogDump GO /****** Object: Stored Procedure dbo.verify_sync_folders Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.verify_sync_folders') and sysstat & 0xf = 4) drop procedure dbo.verify_sync_folders GO /****** Object: Stored Procedure dbo.zip_n_move Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.zip_n_move') and sysstat & 0xf = 4) drop procedure dbo.zip_n_move GO /****** Object: Stored Procedure dbo.check_for_blocks Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.check_for_blocks') and sysstat & 0xf = 4) drop procedure dbo.check_for_blocks GO /****** Object: Stored Procedure dbo.dispatch Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.dispatch') and sysstat & 0xf = 4) drop procedure dbo.dispatch GO /****** Object: Stored Procedure dbo.find_blocker Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.find_blocker') and sysstat & 0xf = 4) drop procedure dbo.find_blocker GO /****** Object: Stored Procedure dbo.isAdminUsingDB Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.isAdminUsingDB') and sysstat & 0xf = 4) drop procedure dbo.isAdminUsingDB GO /****** Object: Stored Procedure dbo.logSyncThenSetDbToHotSite Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.logSyncThenSetDbToHotSite') and sysstat & 0xf = 4) drop procedure dbo.logSyncThenSetDbToHotSite GO /****** Object: Stored Procedure dbo.purgeMSDB Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.purgeMSDB') and sysstat & 0xf = 4) drop procedure dbo.purgeMSDB GO /****** Object: Stored Procedure dbo.rQry Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.rQry') and sysstat & 0xf = 4) drop procedure dbo.rQry GO /****** Object: Stored Procedure dbo.set_to_hot_site Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.set_to_hot_site') and sysstat & 0xf = 4) drop procedure dbo.set_to_hot_site GO /****** Object: Stored Procedure dbo.set_to_load Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.set_to_load') and sysstat & 0xf = 4) drop procedure dbo.set_to_load GO /****** Object: Stored Procedure dbo.set_to_primary Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.set_to_primary') and sysstat & 0xf = 4) drop procedure dbo.set_to_primary GO /****** Object: Stored Procedure dbo.setAllSearchDBsToTruncLog Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.setAllSearchDBsToTruncLog') and sysstat & 0xf = 4) drop procedure dbo.setAllSearchDBsToTruncLog GO /****** Object: Stored Procedure dbo.setSearchDBsToTruncLog Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.setSearchDBsToTruncLog') and sysstat & 0xf = 4) drop procedure dbo.setSearchDBsToTruncLog GO /****** Object: Stored Procedure dbo.setDbToHotSite Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.setDbToHotSite') and sysstat & 0xf = 4) drop procedure dbo.setDbToHotSite GO /****** Object: Stored Procedure dbo.showInputBuffers Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.showInputBuffers') and sysstat & 0xf = 4) drop procedure dbo.showInputBuffers GO /****** Object: Stored Procedure dbo.sp_allspace Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.sp_allspace') and sysstat & 0xf = 4) drop procedure dbo.sp_allspace GO /****** Object: Stored Procedure dbo.verify_dump Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.verify_dump') and sysstat & 0xf = 4) drop procedure dbo.verify_dump GO /****** Object: Stored Procedure dbo.verify_load Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.verify_load') and sysstat & 0xf = 4) drop procedure dbo.verify_load GO /****** Object: Table dbo.databases Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.databases') and sysstat & 0xf = 3) drop table dbo.databases GO /****** Object: Table dbo.drivers Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.drivers') and sysstat & 0xf = 3) drop table dbo.drivers GO /****** Object: Table dbo.in_use Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.in_use') and sysstat & 0xf = 3) drop table dbo.in_use GO /****** Object: Table dbo.logspace Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.logspace') and sysstat & 0xf = 3) drop table dbo.logspace GO /****** Object: Table dbo.messenger Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.messenger') and sysstat & 0xf = 3) drop table dbo.messenger GO /****** Object: Table dbo.persons Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.persons') and sysstat & 0xf = 3) drop table dbo.persons GO /****** Object: Table dbo.PULISTtext Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.PULISTtext') and sysstat & 0xf = 3) drop table dbo.PULISTtext GO /****** Object: Table dbo.scripts Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.scripts') and sysstat & 0xf = 3) drop table dbo.scripts GO /****** Object: Table dbo.semaphore Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.semaphore') and sysstat & 0xf = 3) drop table dbo.semaphore GO /****** Object: Table dbo.servers Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.servers') and sysstat & 0xf = 3) drop table dbo.servers GO /****** Object: Table dbo.test Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.test') and sysstat & 0xf = 3) drop table dbo.test GO /****** Object: Table dbo.textreader Script Date: 11/4/98 1:05:37 PM ******/ if exists (select * from sysobjects where id = object_id('dbo.textreader') and sysstat & 0xf = 3) drop table dbo.textreader GO /****** Object: Table dbo.databases Script Date: 11/4/98 1:05:37 PM ******/ CREATE TABLE dbo.databases ( name varchar (30) NOT NULL , description varchar (255) NULL , compareTableName varchar (30) NULL , CONSTRAINT PK_databases PRIMARY KEY CLUSTERED ( name ) WITH FILLFACTOR = 95 ) GO /****** Object: Table dbo.drivers Script Date: 11/4/98 1:05:37 PM ******/ CREATE TABLE dbo.drivers ( name varchar (30) NOT NULL , description varchar (255) NULL , distributedFlag varchar (5) NULL , CONSTRAINT PK_drivers PRIMARY KEY CLUSTERED ( name ) WITH FILLFACTOR = 95 ) GO /****** Object: Table dbo.in_use Script Date: 11/4/98 1:05:38 PM ******/ CREATE TABLE dbo.in_use ( dbName varchar (30) NOT NULL , server varchar (30) NOT NULL , process varchar (30) NULL , start_time datetime NOT NULL CONSTRAINT DF__in_use__start_ti__22DF33C0 DEFAULT (getdate()), CONSTRAINT PK_in_use PRIMARY KEY CLUSTERED ( dbName, server ) WITH FILLFACTOR = 95 ) GO /****** Object: Table dbo.logspace Script Date: 11/4/98 1:05:38 PM ******/ CREATE TABLE dbo.logspace ( dbname char (30) NOT NULL , logsize numeric(5, 2) NOT NULL , spaceused numeric(10, 8) NOT NULL , status bit NOT NULL , date datetime NOT NULL CONSTRAINT DF_logspace_date DEFAULT (getdate()), CONSTRAINT PK_logspace PRIMARY KEY CLUSTERED ( dbname, date ) WITH FILLFACTOR = 90 ) GO /****** Object: Table dbo.messenger Script Date: 11/4/98 1:05:38 PM ******/ CREATE TABLE dbo.messenger ( id int IDENTITY (1, 1) NOT NULL , spid smallint NULL , info varchar (100) NULL , CONSTRAINT PK_messenger PRIMARY KEY CLUSTERED ( id ) WITH FILLFACTOR = 90 ) GO /****** Object: Table dbo.persons Script Date: 11/4/98 1:05:38 PM ******/ CREATE TABLE dbo.persons ( firstName varchar (20) NOT NULL , lastName varchar (20) NOT NULL , role char (10) NOT NULL , homePhone varchar (15) NULL , pager varchar (15) NULL , emailAddress varchar (50) NULL , CONSTRAINT PK_persons PRIMARY KEY CLUSTERED ( lastName, firstName, role ) WITH FILLFACTOR = 95 ) GO /****** Object: Table dbo.PULISTtext Script Date: 11/4/98 1:05:38 PM ******/ CREATE TABLE dbo.PULISTtext ( text varchar (100) NOT NULL ) GO /****** Object: Table dbo.scripts Script Date: 11/4/98 1:05:39 PM ******/ CREATE TABLE dbo.scripts ( ID int IDENTITY (1, 1) NOT NULL , name varchar (30) NOT NULL , line varchar (100) NOT NULL , CONSTRAINT PK_scripts PRIMARY KEY CLUSTERED ( name, ID ) WITH FILLFACTOR = 90 ) GO /****** Object: Table dbo.semaphore Script Date: 11/4/98 1:05:39 PM ******/ CREATE TABLE dbo.semaphore ( name varchar (30) NOT NULL , status tinyint NOT NULL , CONSTRAINT PK_semaphore PRIMARY KEY CLUSTERED ( name ) WITH FILLFACTOR = 90 ) GO /****** Object: Table dbo.servers Script Date: 11/4/98 1:05:39 PM ******/ CREATE TABLE dbo.servers ( name varchar (30) NOT NULL , description varchar (100) NULL , IPaddr char (15) NULL , dumpShare varchar (10) NULL , dumpDrive char (1) NULL , dumpFolder varchar (30) NULL , loadShare varchar (10) NULL , loadDrive char (1) NULL , loadFolder varchar (30) NULL , CONSTRAINT PK_servers PRIMARY KEY CLUSTERED ( name ) WITH FILLFACTOR = 95 ) GO /****** Object: Table dbo.test Script Date: 11/4/98 1:05:39 PM ******/ CREATE TABLE dbo.test ( text varchar (100) NOT NULL ) GO /****** Object: Table dbo.textreader Script Date: 11/4/98 1:05:39 PM ******/ CREATE TABLE dbo.textreader ( text varchar (100) NOT NULL ) GO /****** Object: Stored Procedure dbo.check_for_blocks Script Date: 11/4/98 1:05:40 PM ******/ CREATE PROCEDURE check_for_blocks @taskName VARCHAR(30) = "Block Monitor", @userName VARCHAR(30) = " ", @mailTo VARCHAR(30) = " " AS /* see if any searchers are blocked */ DECLARE @CMD VARCHAR(255), @CMDstub VARCHAR(255), @CMDstatus INT, @localCMDstatus INT, @spid SMALLINT, @dbid SMALLINT, @blocker SMALLINT, @blockersuid SMALLINT, @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) If (@username = ' ') SELECT @username = 'sa' If (@mailto = ' ') SELECT @mailto = (SELECT emailAddress FROM persons WHERE role='DBA') /* only allow one instance of the block monitor to run */ IF (SELECT COUNT(*) FROM master..sysprocesses WHERE spid <> @@SPID AND program_name LIKE ("SQLEXEC - TSQL Task #%") AND CONVERT(INT,SUBSTRING(RTRIM(program_name), CHARINDEX("#", program_name) + 1, DATALENGTH(program_name) - CHARINDEX("#", program_name))) IN (SELECT id FROM msdb..systasks WHERE name = (@taskName))) = 0 BEGIN /* get the process id and user id for the first block encountered */ SELECT @spid = spid, @dbid = dbid, @blocker = blocked FROM master..sysprocesses WHERE blocked <> 0 AND suid = SUSER_ID(@userName) /* if no blocks, we're done */ IF @spid IS NOT NULL BEGIN /* wait five seconds, if still blocked take action */ WAITFOR DELAY "00:00:10" SELECT @spid = spid, @dbid = dbid, @blocker = blocked FROM master..sysprocesses WHERE blocked <> 0 AND suid = SUSER_ID(@userName) IF @spid IS NOT NULL BEGIN SELECT @blockersuid = suid FROM master..sysprocesses WHERE spid = @blocker SELECT @CMDstatus = 0 SELECT @CMDstub = "Login " + UPPER(@UserName) + " (spid " + CONVERT(VARCHAR(3),@spid) + ") blocked in database " + UPPER(DB_NAME(@dbid)) + " by login " + UPPER(SUSER_NAME(@blockersuid)) + " (spid " + CONVERT(VARCHAR(3),@blocker) + "). [admin.check_for_blocks] " + CONVERT(VARCHAR(20), getdate()) SELECT @CMD = "net send " + @@SERVERNAME + @CMDstub EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 SELECT @CMD = "net send /USERS " + @CMDstub EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output /* send to individual machines or users too ! IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 SELECT @CMD = "net send mike " + @CMDstub EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 SELECT @CMD = "net send roy " + @CMDstub EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 */ IF (@CMDstatus <> 0) -- failed BEGIN SELECT @CMD = CONVERT(CHAR(1),@CMDStatus) + " block alert network broadcast messages FAILED!" + @myName RAISERROR(@CMD,1,2) WITH LOG END /* send a report of the blocking hierarchy */ EXEC master..xp_sendmail @recipients = @mailTo, @subject = 'Blocking Report', @message = @CMDstub, @query = 'admin..find_blocker' /* wait five minutes so the block doesn't flood the network with messages */ WAITFOR DELAY "00:05:00" END -- send messages END -- blocks found END -- block monitor already running EXEC msdb..sp_purgehistory @taskName GO /****** Object: Stored Procedure dbo.dispatch Script Date: 11/4/98 1:05:40 PM ******/ CREATE PROCEDURE dispatch @CMD VARCHAR(255) = NULL, @caller VARCHAR(50) = NULL, @debug VARCHAR(5) = 'false', @mailto VARCHAR(50) = ' ' AS /* Invoke the command line. If the command fails, notify any designate(s) by email and network message. The default behavior of the function is to place invalid command line strings into an email message. Use this behavoir to email admin info message strings.*/ DECLARE @CMDstatus int, @localCMDstatus int If (@mailto = ' ') SELECT @mailto = (SELECT emailAddress FROM persons WHERE role='DBA') IF @debug = 'false' EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output ELSE BEGIN SELECT "**** dispatch command from " + @caller + " ****" SELECT getdate() SELECT @CMD EXEC @CMDstatus = master..xp_cmdshell @CMD SELECT "Command return status: ",@CMDstatus IF (@CMDstatus <> 0) -- failed BEGIN BEGIN SELECT "**** dispatch failed ****" SELECT @CMD SELECT @CMD = 'The ' + @caller + ' process failed at :' + @CMD + '. Status:'+ CONVERT(VARCHAR(2),@CMDstatus) + ' (' + CONVERT(VARCHAR(20),getdate())+ ' admin..dispatch)' EXEC master..xp_sendmail @mailto , @subject="admin..dispatch ALERT!", @message=@CMD RAISERROR(@CMD,1,2) WITH LOG END END END RETURN @CMDstatus GO /****** Object: Stored Procedure dbo.find_blocker Script Date: 11/4/98 1:05:40 PM ******/ CREATE PROCEDURE find_blocker AS /* Query to find spids at head of a blocking chain, their input buffers, and the type of blocking locks they hold */ DECLARE @blocker_spid smallint, @spid SMALLINT, @i_buff_string char(30), @CMD VARCHAR(255) SET NOCOUNT ON /* Get all blocked spids */ SELECT DISTINCT spid, suid, dbid, blocked INTO #blk FROM master..sysprocesses (NOLOCK) WHERE blocked <> 0 OR (spid IN (SELECT blocked FROM master..sysprocesses where blocked <> 0) AND blocked = 0) /* list all blocked spids */ SELECT "Blocked Processes" SELECT spid as "BLOCKED SPID", SUSER_NAME(suid) as "LOGIN NAME", DB_NAME(dbid) as "DB NAME", blocked as "BLOCKING SPID" FROM #blk WHERE blocked <> 0 /* dump all input buffers for spids that are blocked */ SELECT "Last Command from Blocked Processes" SELECT @spid = (SELECT MIN(spid) FROM #blk WHERE blocked <> 0) WHILE @spid IS NOT NULL BEGIN SELECT @CMD = 'SELECT "BLOCKED SPID ' + CONVERT(VARCHAR(3),@spid) + '"' + " DBCC INPUTBUFFER(" + CONVERT(VARCHAR(3),@spid) + ") " EXEC (@CMD) SELECT @spid = (SELECT MIN(spid) FROM #blk WHERE spid > @spid AND blocked <> 0) END /* For each spid at the head of a blocking chain print the type of blocking locks it holds */ /* dump all input buffers for spids that are blocked */ SELECT "Locks held by involved processes" SELECT spid, l.type, locktype = name, table_id = id, page, dbid FROM master..syslocks l, master.dbo.spt_values v WHERE l.type = v.number AND v.type='L' AND (l.type & 256)=256 AND spid IN (SELECT blocked FROM #blk) ORDER BY spid /* delete all blocking spids except blockers */ DELETE FROM #blk WHERE blocked <> 0 /* get each spid from sysprocesses which is referenced in the "blocked" column of #blk. This should be the head of each blocking chain */ SELECT "Blocking Process(es)" SELECT "BLOCKED SPID"=spid, "LOGIN NAME"=SUSER_NAME(suid), "DB NAME"=DB_NAME(dbid), "BLOCKING SPID"=blocked FROM #blk WHERE blocked = 0 /* For each spid at the head of a blocking chain print its input buffer to show what query it's running */ SELECT " Last Command from Blocking Processes" SELECT @spid = (SELECT MIN(spid) FROM #blk) WHILE @spid IS NOT NULL BEGIN SELECT @CMD = 'SELECT "BLOCKED SPID ' + CONVERT(VARCHAR(3),@spid) + '"' + " DBCC INPUTBUFFER(" + CONVERT(VARCHAR(3),@spid) + ") " EXEC (@CMD) SELECT @spid = (SELECT MIN(spid) FROM #blk WHERE spid > @spid) END DROP table #blk GO /****** Object: Stored Procedure dbo.isAdminUsingDB Script Date: 11/4/98 1:05:40 PM ******/ CREATE PROCEDURE isAdminUsingDB @dbName VARCHAR(30) = ' ', @source VARCHAR(30) = ' ', @target VARCHAR(30) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @dbSyncTask VARCHAR(50), @logSyncTask VARCHAR(50), @maintTask VARCHAR(50), @val INT, @spid SMALLINT, @CMD VARCHAR(255), @myName VARCHAR(50) /* Procedure isAdminUSingDB will return zero if there are no competing admin processes currently active that use the specified database on the source or target server. Can't use systasks, because a hard failure can render this info corrupt. */ SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @dbSyncTask = "dbSync " + @dbName + "%" SELECT @logSyncTask = "logSync " + @dbName + "%" SELECT @maintTask = "Maintain " + @source + '.' + @dbName + "%" /* check for a t-SQL competitor */ SELECT @val = (SELECT count(*) FROM master..sysprocesses WHERE spid <> @@SPID AND program_name LIKE ("SQLEXEC - TSQL Task #%") AND CONVERT(INT,SUBSTRING(RTRIM(program_name), CHARINDEX("#", program_name) + 1, DATALENGTH(program_name) - CHARINDEX("#", program_name))) IN (SELECT id FROM msdb..systasks WHERE ( name LIKE (@dbSyncTask) AND ( CHARINDEX(@source, name) > 0 OR CHARINDEX(@target, name) > 0)) OR ( name LIKE (@logSyncTask) AND ( CHARINDEX(@source, name) > 0 OR CHARINDEX(@target, name) > 0)) OR name LIKE (@maintTask))) IF (@val <> 0) BEGIN /* wait 2 seconds and recheck - could be that another job submitted this task and it hasn't cleaned up yet */ WAITFOR DELAY "00:00:02" SELECT @val = (SELECT count(*) FROM master..sysprocesses WHERE spid <> @@SPID AND program_name LIKE ("SQLEXEC - TSQL Task #%") AND CONVERT(INT,SUBSTRING(RTRIM(program_name), CHARINDEX("#", program_name) + 1, DATALENGTH(program_name) - CHARINDEX("#", program_name))) IN (SELECT id FROM msdb..systasks WHERE ( name LIKE (@dbSyncTask) AND ( CHARINDEX(@source, name) > 0 OR CHARINDEX(@target, name) > 0)) OR ( name LIKE (@logSyncTask) AND ( CHARINDEX(@source, name) > 0 OR CHARINDEX(@target, name) > 0)) OR name LIKE (@maintTask))) END IF (@val = 0) BEGIN /* still could be a CMDEXEC competitor */ IF (SELECT count(*) FROM master..sysprocesses WHERE program_name = ("ISQL-32") AND spid <> @@SPID) <> 0 BEGIN /* wait 2 seconds and recheck - could be that another job submitted this task and it hasn't cleaned up yet */ WAITFOR DELAY "00:00:02" IF (SELECT count(*) FROM master..sysprocesses WHERE program_name = ("ISQL-32") AND spid <> @@SPID) <> 0 BEGIN /* temp table needs to allow nulls in case a spid that qualified for the cursor ends before the count*/ CREATE TABLE #string(name VARCHAR(255) NULL) DECLARE CmdExec_cursor CURSOR FOR SELECT spid FROM master..sysprocesses WHERE program_name = "ISQL-32" AND spid <> @@SPID OPEN CmdExec_cursor FETCH NEXT FROM CmdExec_cursor INTO @spid WHILE (@@fetch_status <> -1) BEGIN SELECT @CMD = "DBCC INPUTBUFFER(" + CONVERT(VARCHAR(6), @spid) + ")" INSERT #string EXEC(@CMD) FETCH NEXT FROM CmdExec_cursor INTO @spid END CLOSE CmdExec_cursor DEALLOCATE CmdExec_cursor SELECT @val = (SELECT count(*) FROM #string WHERE ( name LIKE (@dbSyncTask) AND ( CHARINDEX(@source, name) > 0 OR CHARINDEX(@target, name) > 0)) OR ( name LIKE (@logSyncTask) AND ( CHARINDEX(@source, name) > 0 OR CHARINDEX(@target, name) > 0)) OR name LIKE (@maintTask)) DROP TABLE #string END END END IF @debug = 'true' BEGIN SELECT "stored procedure ",@myName SELECT "number of competing processes = ", @val END RETURN @val GO /****** Object: Stored Procedure dbo.logSyncThenSetDbToHotSite Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE logSyncThenSetDbToHotSite @dbName VARCHAR(30) = ' ' AS EXEC logSync @dbName EXEC setDbToHotSite GO /****** Object: Stored Procedure dbo.purgeMSDB Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROC purgeMSDB @source VARCHAR(30) = ' ', @sourcePassword VARCHAR(30) = ' ', @DeleteBeforeDate VARCHAR(30) = '1/1/1970', @debug VARCHAR(5) = 'false' WITH RECOMPILE AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @CMDstatus = 0 IF (@source = ' ') BEGIN SELECT @CMD = "Invalid parameter list provided for procedure " + @myName RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* default to keep 10 days of history */ IF (@DeleteBeforeDate = '1/1/1970') SELECT @DeleteBeforeDate = DATEADD(dd,-10,getdate()) SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + "dump tran msdb with truncate_only" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + "sp_configure 'allow updates',1" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"reconfigure with override"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'DELETE msdb.dbo.sysbackupdetail WHERE backup_id IN ' + '(SELECT backup_id FROM msdb.dbo.sysbackuphistory ' + "WHERE backup_start <= '" + @DeleteBeforeDate + "')" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = "DELETE msdb.dbo.sysbackuphistory WHERE backup_start <= '" + @DeleteBeforeDate + "'" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'DELETE msdb.dbo.sysrestoredetail WHERE restore_id IN ' + '(SELECT restore_id FROM msdb.dbo.sysrestorehistory ' + "WHERE backup_start <= '" + @DeleteBeforeDate + "')" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = "DELETE msdb.dbo.sysRestorehistory WHERE backup_start <= '" + @DeleteBeforeDate + "'" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + "sp_configure 'allow updates',0" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"reconfigure with override"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus GO /****** Object: Stored Procedure dbo.rQry Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE rQry @CMD VARCHAR(255) AS EXEC (@CMD) GO /****** Object: Stored Procedure dbo.set_to_hot_site Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE set_to_hot_site @dbName VARCHAR(30) = ' ', -- the database name on both systems @target VARCHAR(30) = ' ', -- the source server name @targetPassword VARCHAR(30) = ' ', -- sa password on source @debug VARCHAR(5) = 'false' -- debug this procedure AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @myName VARCHAR(50) -- name of this function IF (@dbName <> ' ') AND (@target <> ' ') BEGIN SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF DB_ID(@dbName) < 6 BEGIN SELECT @CMD = @myName + ' cannot be used on ' + @dbName + '. Only user databases can be safely moved via dump/load.' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* make sure the target database is read only (or not a permanent data storage location), has checkpoint on recovery disabled, has select into/bulkcopy disabled, has truncate log on checkpoint disabled, */ IF @dbName IN ('Put', --script edit requited 'your','volitile', --script edit required 'data','databases', --script edit required 'here', 'to' ,'allow', --script edit required 'bcp') --script edit required BEGIN SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'read only',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'select into/bulkcopy',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'trunc. log on chkpt.',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END ELSE -- all other dbs BEGIN SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'read only',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'select into/bulkcopy',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'trunc. log on chkpt.',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END END ELSE BEGIN PRINT " usage: set_to_hot_site" PRINT " [@dbname = ]," PRINT " [@target = ]," PRINT " [@targetPassword = ]," PRINT " [@debug = ]," RETURN 1 END RETURN GO /****** Object: Stored Procedure dbo.set_to_load Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE set_to_load @dbName VARCHAR(30) = ' ', -- the database name on both systems @target VARCHAR(30) = ' ', -- the source server name @targetPassword VARCHAR(30) = ' ', -- sa password on source @debug VARCHAR(5) = 'false' -- debug this procedure AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @myName VARCHAR(50) -- name of this function IF (@dbName <> ' ') AND (@target <> ' ') BEGIN SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF DB_ID(@dbName) < 6 BEGIN SELECT @CMD = @myName + ' cannot be used on ' + @dbName + '. Only user databases can be safely moved via dump/load.' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* make sure the target database is not read only has checkpoint on recovery disabled has select into/bulkcopy disabled has truncate log on checkpoint disabled and has minimum log space */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'read only',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + ' with TRUNCATE_ONLY' + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'select into/bulkcopy',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'trunc. log on chkpt.',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'no chkpt on recovery',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END ELSE BEGIN PRINT " usage: set_to_load" PRINT " [@dbname = ]," PRINT " [@target = ]," PRINT " [@targetPassword = ]," PRINT " [@debug = ]," RETURN 1 END RETURN GO /****** Object: Stored Procedure dbo.set_to_primary Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE set_to_primary @dbName VARCHAR(30) = ' ', -- the database name on both systems @source VARCHAR(30) = ' ', -- the source server name @sourcePassword VARCHAR(30) = ' ', -- sa password on source @debug VARCHAR(5) = 'false' -- debug this procedure AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @myName VARCHAR(50) -- name of this function IF (@dbName <> ' ') AND (@source <> ' ') BEGIN SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF DB_ID(@dbName) < 6 BEGIN SELECT @CMD = @myName + ' cannot be used on ' + @dbName + '. Only user databases can be safely moved via dump/load.' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* make sure the source database has select into/bulkcopy disabled truncate log on checkpoint disabled (except this admin db) and no checkpoint on recovery enabled */ IF @dbName = 'admin' BEGIN SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'select into/bulkcopy',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'trunc. log on chkpt.',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END ELSE BEGIN SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'select into/bulkcopy',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'trunc. log on chkpt.',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'no chkpt on recovery',true" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END ELSE BEGIN PRINT " usage: set_to_primary" PRINT " [@dbname = ]," PRINT " [@source = ]," PRINT " [@sourcePassword = ]," PRINT " [@debug = ]," RETURN 1 END RETURN GO /****** Object: Stored Procedure dbo.setAllSearchDBsToTruncLog Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE setAllSearchDBsToTruncLog AS DECLARE @CMD VARCHAR(255), @CMDstatus int, @target VARCHAR(30), @targetId INT, @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) /* set the local transient data stores to trunc. log on chkpt */ EXEC admin.dbo.setSearchDBsToTruncLog /* set all remote transient data stores to trunc. log on chkpt */ SELECT @targetID = (SELECT MIN(srvid) FROM master..sysservers WHERE srvid > 0 AND srvstatus = 1) SELECT @target = (SELECT srvname FROM master..sysservers WHERE srvid = @targetId) WHILE @targetID IS NOT NULL BEGIN SELECT @CMD = @target + ".admin.dbo.setSearchDBsToTruncLog" EXEC (@CMD) SELECT @targetID = (SELECT MIN(srvid) FROM master..sysservers WHERE srvid > @targetId AND srvstatus = 1) IF @targetId IS NOT NULL SELECT @target = (SELECT srvname FROM master..sysservers WHERE srvid = @targetId) END GO /****** Object: Stored Procedure dbo.setSearchDBsToTruncLog Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE setSearchDBsToTruncLog AS DECLARE @dbid INT, @CMD VARCHAR(255), @CMDstatus int SET NOCOUNT ON SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases WHERE name LIKE "%SP" OR name LIKE "%RES") WHILE @dbid IS NOT NULL BEGIN SELECT @CMD = 'sp_dboption ' + DB_NAME(@dbid) + ',"trunc. log on chkpt.",true' EXEC (@CMD) SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases WHERE dbid > @dbid AND ( name LIKE "%SP" OR name LIKE "%RES")) END GO /****** Object: Stored Procedure dbo.setDbToHotSite Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE setDbToHotSite @dbName VARCHAR(30) = NULL, -- the database name on both systems @target VARCHAR(30) = 'HS_LEIN', -- the target server name @targetPassword VARCHAR(30) = ' ', -- sa password on target @debug VARCHAR(5) = 'false' -- debug flag AS DECLARE @CMD VARCHAR(255), -- working var to build and pass strings @CMDstatus INT, -- working var for return status of an @CMD @myName VARCHAR(50) -- name of this function IF @dbName IS NOT NULL BEGIN /* Procedure will dump a database and restore the dump to a hot backup destination. Both the source and target databases will be configured to support an async log dump and restore process. All dumps will be archived for the source and target systems. */ SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) /* make sure the target database is read/write has checkpoint on recovery enabled has select into/bulkcopy disabled has truncate log on checkpoint disabled and has log space */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'read only',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + ' with TRUNCATE_ONLY' + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'select into/bulkcopy',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'trunc. log on chkpt.',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + 'sp_dboption ' + @dbName + ",'no chkpt on recovery',false" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* Turn off all tasks that init logSync or dbSync to this system */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + "sp_configure 'allow updates',1" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"reconfigure with override"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = "UPDATE msdb.dbo.systasks SET enabled = 0 " + "WHERE name LIKE 'logSync%' " + "OR name LIKE 'dbSync%'" SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + "sp_configure 'allow updates',0" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"reconfigure with override"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END GO /****** Object: Stored Procedure dbo.showInputBuffers Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE showInputBuffers AS DECLARE @spid SMALLINT, @CMD VARCHAR(50) SET NOCOUNT ON SELECT @spid = (SELECT MIN(spid) from master..sysprocesses WHERE spid > 4) WHILE @spid IS NOT NULL BEGIN SELECT 'spid: ',@spid SELECT @CMD = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(5), @spid) + ')' EXEC (@CMD) SELECT @spid = (SELECT MIN(spid) from master..sysprocesses WHERE spid > @spid) END GO /****** Object: Stored Procedure dbo.sp_allspace Script Date: 11/4/98 1:05:41 PM ******/ create procedure sp_allspace -- IMPORTANT! usage info. should be updated before running me. -- Empty tables are ommitted from the results. as declare @objname varchar(92) -- The object we want size on. declare @id int -- The object id of @objname. declare @type smallint -- The object type. declare @pages int -- Working variable for size calc. declare @dbname varchar(30) declare @dbsize dec(15,0) declare @trows dec(15,0) declare @tall dec(15,0) declare @tdata dec(15,0) declare @tidx dec(15,0) declare @tuu dec(15,0) declare @srows dec(15,0) declare @sall dec(15,0) declare @sdata dec(15,0) declare @sidx dec(15,0) declare @suu dec(15,0) declare @irows dec(15,0) declare @iall dec(15,0) declare @idata dec(15,0) declare @iidx dec(15,0) declare @iuu dec(15,0) declare @iname varchar(20) declare @msg varchar(80) declare AllTables insensitive cursor for select name from sysobjects order by name open AllTables /* ** We need to create a temp table to do the calculation. ** reserved: sum(reserved) where indid in (0, 1, 255) ** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text) ** indexp: sum(used) where indid in (0, 1, 255) - data ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ create table #spt_space ( name varchar(92), rows int null, reserved dec(15) null, data dec(15) null, indexp dec(15) null, unused dec(15) null ) set nocount on select getdate() fetch next from AllTables into @objname /* ** Insert the row for the table */ insert into #spt_space (name,rows,reserved,data,indexp,unused) values ("zzUser Table Totals",0,0,0,0,0) insert into #spt_space (name,rows,reserved,data,indexp,unused) values ("zzSystem Table Totals",0,0,0,0,0) while @@fetch_status = 0 begin /* ** Find the object. */ select @id = null select @id = id, @type = sysstat & 0xf from sysobjects where id = object_id(@objname) /* ** See if it's a space object. ** types are: ** 1 - system table ** 2 - view ** 3 - user table ** 4 - sproc ** 6 - default ** 7 - rule ** 8 - trigger ** 9 - primary key ** 10 - check constraint ** 11 - foreign key ** 12 - replication filter stored proc */ if @type in (1,3) begin /* ** Insert the row for the table */ insert into #spt_space (name) values (@objname) /* ** Now calculate the summary data. ** reserved: sum(reserved) where indid in (0, 1, 255) */ /* insert into #spt_space (reserved) */ select @iall = sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id update #spt_space set reserved = @iall where name = @objname /* Update Totals */ if @type = 3 update #spt_space set reserved = reserved + @iall where name = "zzUser Table Totals" else update #spt_space set reserved = reserved + @iall where name = "zzSystem Table Totals" /* ** data: sum(dpages) where indid < 2 ** + sum(used) where indid = 255 (text) */ select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages where name = @objname /* Update Totals */ if @type = 3 update #spt_space set data = data + @pages where name = "zzUser Table Totals" else update #spt_space set data = data + @pages where name = "zzSystem Table Totals" /* index: sum(used) where indid in (0, 1, 255) - data */ select @iidx = sum(used) from sysindexes where indid in (0, 1, 255) and id = @id update #spt_space set indexp = @iidx - data where name = @objname /* Update Totals */ if @type = 3 update #spt_space set indexp = indexp + (@iidx - data) where name = "zzUser Table Totals" else update #spt_space set indexp = indexp + (@iidx - data) where name = "zzSystem Table Totals" /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - @iidx where name = @objname /* Update Totals */ if @type = 3 update #spt_space set unused = unused + (reserved - @iidx) where name = "zzUser Table Totals" else update #spt_space set unused = unused + (reserved - @iidx) where name = "zzSystem Table Totals" select @irows = i.rows from sysindexes i where i.indid < 2 and i.id = @id update #spt_space set rows = @irows where name = @objname /* Update totals */ if @type = 3 update #spt_space set rows = rows + @irows where name = "zzUser Table Totals" else update #spt_space set rows = rows + @irows where name = "zzSystem Table Totals" end fetch next from AllTables into @objname end /* end while loop here and print out totals */ select "Table" = substring(#spt_space.name, 1, 20), "Rows" = str(rows,12), "Reserved KB" = (str(reserved * d.low / 1024.,15)), "Data Space KB" = (str(data * d.low / 1024.,15)), "Index Space KB" = (str(indexp * d.low / 1024.,15)), "Unused Space KB" = (str(unused * d.low / 1024.,15)) from #spt_space, master.dbo.spt_values d where (d.number = 1 and d.type = 'E') and rows <> 0 -- do not show empty tables order by #spt_space.name close AllTables deallocate AllTables return (0) GO /****** Object: Stored Procedure dbo.verify_dump Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE verify_dump @source VARCHAR(30) = ' ', @device VARCHAR(80) = ' ', @found INT OUTPUT, @debug VARCHAR(5) = 'false' AS DECLARE @CMDstatus INT, @timeWindowStart VARCHAR(20), @myName VARCHAR(50) IF (@source = ' ') OR (@device = ' ') RETURN 1 SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF @debug = 'true' BEGIN SELECT '@myName: ', @myName SELECT '@source: ', @source SELECT '@device: ', @device END IF @source = @@SERVERNAME BEGIN /* already dumped in last hour or so minutes? */ SELECT @timeWindowStart = DATEADD(minute, -59, GETDATE()) SELECT @found = (SELECT COUNT(d.backup_id) FROM msdb..sysbackupdetail d, msdb..sysbackuphistory h WHERE d.device_name = @device AND d.backup_id = h.backup_id AND h.backup_finish > @timeWindowStart) RETURN END --script edit required IF (@source = 'servername1') EXEC servername1.admin.dbo.verify_dump @source, @device, @found OUTPUT, @debug --script edit required IF (@source = 'servername2') EXEC servername2.admin.dbo.verify_dump @source, @device, @found OUTPUT, @debug --script edit required IF (@source = 'servername3') EXEC servername3.admin.dbo.verify_dump @source, @device, @found OUTPUT, @debug --script edit required --and so on (no way to get the remote exec to work without hard coding :( ) --I need to rewrite to use run_script_remote and select from servers IF (@source = 'servername4') EXEC servername4.admin.dbo.verify_dump @source, @device, @found OUTPUT, @debug IF (@debug='true') SELECT "@found = ",@found GO /****** Object: Stored Procedure dbo.verify_load Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE verify_load @target VARCHAR(30) = ' ', @device VARCHAR(80) = ' ', @found INT OUTPUT, @debug VARCHAR(5) = 'false' AS DECLARE @CMDstatus INT, @timeWindowStart VARCHAR(20), @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF (@debug='true') BEGIN SELECT "INPUT PARMS FOR ", @myName SELECT "Current server: ", @@SERVERNAME SELECT "target: ",@target SELECT "device: ",@device END IF (@target = ' ') OR (@device = ' ') RETURN 1 SELECT @device = "%" + SUBSTRING(@device, DATALENGTH(@device) - 20 , 21) IF @target = @@SERVERNAME BEGIN SELECT @myName = 'admin.verify_load' /* in last 30 minutes? */ SELECT @timeWindowStart = DATEADD(minute, -40, GETDATE()) SELECT @found = (SELECT COUNT(d.restore_id) FROM msdb..sysrestoredetail d, msdb..sysrestorehistory h WHERE d.device_name LIKE @device AND d.restore_id = h.restore_id AND h.restore_date > @timeWindowStart) RETURN END --script edit required IF (@target = 'servername1') EXEC servername1.admin.dbo.verify_dump @target, @device, @found OUTPUT, @debug --script edit required IF (@target = 'servername2') EXEC servername2.admin.dbo.verify_dump @target, @device, @found OUTPUT, @debug --script edit required IF (@target = 'servername3') EXEC servername3.admin.dbo.verify_dump @target, @device, @found OUTPUT, @debug --script edit required --and so on (no way to get the remote exec to work without hard coding :( ) --I need to rewrite to use run_script_remote and select from servers IF (@target = 'servername4') EXEC servername4.admin.dbo.verify_dump @target, @device, @found OUTPUT, @debug IF (@debug='true') BEGIN SELECT "INTERNAL VARS FOR ", @myName SELECT "Current server: ", @@SERVERNAME SELECT "parsed device: ",@device SELECT "found: ",@found END GO /****** Object: Stored Procedure dbo.dbSync_driver Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE dbSync_driver @dbName VARCHAR(30) = ' ', @source VARCHAR(30) = ' ', @target VARCHAR(30) = ' ', @debug VARCHAR(50) = 'false' as DECLARE @procedure VARCHAR(30), @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(30) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID) SELECT @procedure = 'dbSync' INSERT in_use VALUES (@dbName, @source, @procedure, getdate()) SELECT @CMDstatus = @@ERROR IF (@CMDstatus=0) BEGIN INSERT in_use VALUES (@dbName, @target, @procedure , getdate()) SELECT @CMDstatus = @@ERROR END IF (@CMDstatus = 0) BEGIN SELECT @CMD = @procedure + ' ' + @dbName + ', ' + @source + ', ' + @target + ', ' + '@debug=' + @debug EXEC (@CMD) SELECT @CMDstatus = @@ERROR IF @CMDstatus <> 0 SELECT "return code: ",@CMDstatus DELETE in_use WHERE dbName = @dbName AND (server = @source OR server = @target) AND process = @procedure END ELSE BEGIN SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " + CONVERT(VARCHAR(5), @CMDstatus) + ", " + @myName + ")" IF (@CMDstatus = 2627) SELECT @CMD = 'An adminstrative subsystem process is already ' + 'active in database ' + @dbName + ' on server ' + @source + '. Only one admin process may be active ' + 'in any database at any time.(' + @myName + ')' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END GO /****** Object: Stored Procedure dbo.delete_archive Script Date: 11/4/98 1:05:41 PM ******/ CREATE PROCEDURE delete_archive @archive VARCHAR(80) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @myName VARCHAR(50), @CMD VARCHAR(255), @CMDstatus INT /* maintain the archive remove files with extension of .zzz if .zip exist. rename files with extension of .zip to .zzz */ SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @CMD = 'dir ' + @archive + '\*.zip' EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus = 0) -- found BEGIN SELECT @CMD = 'dir ' + @archive + '\*.zzz' EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus = 0) -- found BEGIN SELECT @CMD = 'del ' + @archive + '\*.zzz' EXEC @CMDstatus = dispatch @CMD, @myName, @debug END SELECT @CMD = 'ren ' + @archive + '\*.zip '+ '*.zzz' EXEC @CMDstatus = dispatch @CMD, @myName, @debug END RETURN GO /****** Object: Stored Procedure dbo.dumpDatabase Script Date: 11/4/98 1:05:42 PM ******/ CREATE PROCEDURE dumpDatabase @db VARCHAR(30)= ' ', @server VARCHAR(30) = ' ', @Password VARCHAR(30) = ' ', @Folder VARCHAR(30) = ' ', @drive VARCHAR(5) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @CMD VARCHAR(255), @CMDstatus INT, @dumpName VARCHAR(80), @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF (@db = ' ') OR (@server = ' ') OR (@Folder = ' ') OR (@drive = ' ') RETURN 1 /* dump the database's log on the source system */ SELECT @CMD = 'isql -Usa -P' + @Password + ' -S' + @server + ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @db + ' with TRUNCATE_ONLY' + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* don't need to dump transient dbs */ --script edit required IF (@db NOT LIKE "%Res") OR (@db = "exceptThisRes") BEGIN SELECT @dumpName = @drive + ':\' + @Folder + '\' + @db + '\' + SUBSTRING(@db,1,4) +"dump.DAT" SELECT @CMD = 'isql -Usa -P' + @Password + ' -S' + @server + ' -dmaster -Q"DUMP DATABASE ' + @db + " TO DISK = '" + @dumpName + "' WITH INIT, RETAINDAYS = 0" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* make sure the dump was logged to msdb */ EXEC verify_dump @server, @dumpName, @CMDstatus OUTPUT, @debug IF (@debug = 'true') SELECT "No of dumps found = ", @CMDstatus IF (@CMDstatus = 0) BEGIN SELECT @CMD = 'Dump of database ' + @db + ' not found in msdb..sysbackuphistory on ' + @server + '. The ' + @myName + ' process has been aborted.' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END IF (@CMDstatus > 1) BEGIN SELECT @CMD = 'Unintended dumps of database ' + @db + ' on server ' + @server + ' may have occurred in the last 30 minutes. (' + @myName + ')' EXEC @CMDstatus = dispatch @CMD, @myName, @debug END END -- do not dump transient db ELSE IF (@debug='true') SELECT "This transient db will not be dumped to disk" RETURN GO /****** Object: Stored Procedure dbo.free_semaphore Script Date: 11/4/98 1:05:42 PM ******/ CREATE PROCEDURE free_semaphore AS /* restart recovery task to clean up falsly held semaphores */ UPDATE semaphore SET status = 0 GO /****** Object: Stored Procedure dbo.help_users_all Script Date: 11/4/98 1:05:42 PM ******/ CREATE PROCEDURE help_users_all @saPassword VARCHAR(20) = ' ' AS DECLARE @dbid INT, @drive CHAR(1), @folder VARCHAR(50), @lastdbid INT, @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID) SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases) /* build an outfile path for this server */ SELECT @drive = dumpDrive, @folder = dumpFolder FROM admin..servers WHERE name = @@SERVERNAME SELECT @CMD = "dir " + @drive + ":\" + @folder + "\master\help_users_all.out" EXEC @CMDstatus = dispatch @CMD, @myName IF (@CMDstatus = 0) BEGIN SELECT @CMD = "del " + @drive + ":\" + @folder + "\master\help_users_all.out" EXEC @CMDstatus = dispatch @CMD, @myName IF (@CMDstatus <> 0) return @CMDstatus END WHILE @dbid IS NOT NULL BEGIN SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + ' -Q"SELECT dbName=' + "'" + DB_NAME(@dbid) + "' USE " + DB_NAME(@dbid) + +' EXEC sp_helpuser">>' + @drive + ':\' + @folder + '\master\help_users_all.out' EXEC @CMDstatus = dispatch @CMD, @myName IF (@CMDstatus <> 0) return @CMDstatus SELECT @lastdbid = @dbid SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases WHERE dbid > @lastdbid) END GO /****** Object: Stored Procedure dbo.hold_semaphore Script Date: 11/4/98 1:05:42 PM ******/ CREATE PROCEDURE hold_semaphore @name VARCHAR(30) = 'textreader' AS /* wait until the requested semaphore is available then grab it */ SET NOCOUNT ON WHILE (SELECT status FROM semaphore WHERE name = @name) = 1 BEGIN WAITFOR DELAY "00:00:10" END UPDATE semaphore SET status = 1 WHERE name = @name AND status = 0 GO /****** Object: Stored Procedure dbo.logSync_driver Script Date: 11/4/98 1:05:42 PM ******/ CREATE PROCEDURE logSync_driver @dbName VARCHAR(30) = ' ', @source VARCHAR(30) = ' ', @target VARCHAR(30) = ' ', @debug VARCHAR(50) = 'false' AS DECLARE @procedure VARCHAR(30), @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(30) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID) SELECT @procedure = 'logSync' INSERT in_use VALUES (@dbName, @source, @procedure, getdate()) SELECT @CMDstatus = @@ERROR IF (@CMDstatus=0) BEGIN INSERT in_use VALUES (@dbName, @target, @procedure , getdate()) SELECT @CMDstatus = @@ERROR END IF (@CMDstatus = 0) BEGIN SELECT @CMD = @procedure + ' ' + @dbName + ', ' + @source + ', ' + @target + ', ' + '@debug=' + @debug EXEC (@CMD) SELECT @CMDstatus = @@ERROR IF @CMDstatus <> 0 SELECT "return code: ",@CMDstatus DELETE in_use WHERE dbName = @dbName AND (server = @source OR server = @target) AND process = @procedure END ELSE BEGIN SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " + CONVERT(VARCHAR(5), @CMDstatus) + ", " + @myName + ")" IF (@CMDstatus = 2627) SELECT @CMD = 'An adminstrative subsystem process is already ' + 'active in database ' + @dbName + ' on server ' + @source + '. Only one admin process may be active ' + 'in any database at any time.(' + @myName + ')' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END GO /****** Object: Stored Procedure dbo.maintenance_driver Script Date: 11/4/98 1:05:42 PM ******/ CREATE PROCEDURE maintenance_driver @dbName VARCHAR(30) = ' ', @source VARCHAR(30) = ' ', @debug VARCHAR(50) = 'false' as DECLARE @procedure VARCHAR(30), @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(30) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID) SELECT @procedure='maintenance' INSERT in_use VALUES (@dbName, @source, @procedure, getdate()) SELECT @CMDstatus = @@ERROR IF (@CMDstatus = 0) BEGIN SELECT @CMD = @procedure + ' ' + @dbName + ', ' + @source + ', ' + '@debug=' + @debug EXEC (@CMD) SELECT @CMDstatus = @@ERROR IF @CMDstatus <> 0 SELECT "return code: ",@CMDstatus DELETE in_use WHERE dbName = @dbName AND server = @source AND process = @procedure END ELSE BEGIN SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " + CONVERT(VARCHAR(5), @CMDstatus) + ", " + @myName + ")" IF (@CMDstatus = 2627) SELECT @CMD = 'An adminstrative subsystem process is already ' + 'active in database ' + @dbName + ' on server ' + @source + '. Only one admin process may be active ' + 'in any database at any time.(' + @myName + ')' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END GO /****** Object: Stored Procedure dbo.release_semaphore Script Date: 11/4/98 1:05:43 PM ******/ CREATE PROCEDURE release_semaphore @name VARCHAR(30) = 'textreader' AS /* release the specified semaphore */ SET NOCOUNT ON UPDATE semaphore SET status = 0 WHERE name = @name AND status = 1 GO /****** Object: Stored Procedure dbo.reset_subsystem Script Date: 11/4/98 1:05:43 PM ******/ CREATE PROCEDURE reset_subsystem AS UPDATE admin.dbo.semaphore SET status=0 DELETE admin.dbo.in_use GO /****** Object: Stored Procedure dbo.setLogDump Script Date: 11/4/98 1:05:43 PM ******/ CREATE PROCEDURE setLogDump @dbName VARCHAR(30) = ' ', @dumpPath VARCHAR(50) = ' ', -- fully qualified dump path @loadPath VARCHAR(50) = ' ', -- fully qualified load path @name VARCHAR(30) = ' ' OUTPUT, @debug VARCHAR(5) = 'false' AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @localCMDstatus INT, -- return status of command prompt @dumpDevice VARCHAR(80), -- fully qualified dump device @dbDumpName VARCHAR(20), -- database dump file name @dbDumpTemp VARCHAR(20), -- database dump file name @loadDevice VARCHAR(80), -- fully qualified load device @currentDate DATETIME, -- used to make dump file name @dumpSeq TINYINT, -- used to make dump file name @myName VARCHAR(50) -- this procedures name /* find a dump file name that is not in use. Look in the dump and load folders. This will support up to 253 log dump files only the first 99 will be 8.3 file name */ IF (@dbName = ' ') OR (@dumpPath = ' ') OR (@loadPath = ' ') RETURN 1 SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @dbDumpName = SUBSTRING(@dbName,1,4) +"dump.DAT" SELECT @dbDumpTemp = SUBSTRING(@dbName,1,4) +"dump.TMP" SELECT @currentDate = (SELECT getdate()) SELECT @CMDstatus = 0 SELECT @dumpSeq = 1 WHILE (@CMDstatus = 0) AND (@dumpSeq < 254) -- success BEGIN -- look in the dump directory SELECT @name = CONVERT(varchar(2), datepart(month,@currentDate)) + CONVERT(varchar(2), datepart(day,@currentDate)) + SUBSTRING(CONVERT(char(4), datepart(year,@currentDate)),3,2) + CONVERT(varchar(3), @dumpSeq) + ".DAT" SELECT @dumpDevice = @dumpPath + '\' + @name SELECT @CMD = 'dir ' + @dumpdevice EXEC @CMDstatus=master..xp_cmdshell @CMD, no_output IF (@CMDstatus <> 0) -- did not find it BEGIN -- look in load folder SELECT @loadDevice = @loadPath + '\' + @name SELECT @CMD = 'dir ' + @loaddevice EXEC @CMDstatus=master..xp_cmdshell @CMD, no_output IF (@CMDstatus = 0) -- found it SELECT @dumpSeq = @dumpSeq + 1 -- try next name ELSE BEGIN IF (@dumpseq = 1) -- remove previous log dumps BEGIN SELECT @CMD = 'dir ' + @dumpPath + '\' + @dbDumpName EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'ren ' + @dumpPath + '\' + @dbDumpName + ' ' + @dbDumpTemp EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END SELECT @CMD = 'dir ' + @dumpPath + '\*.DAT' EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'del ' + @dumpPath + '\*.DAT' EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END SELECT @CMD = 'dir ' + @dumpPath + '\*.OUT' EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'del ' + @dumpPath + '\*.OUT' EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END SELECT @CMD = 'dir ' + @dumpPath + '\' + @dbDumpTemp EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'ren ' + @dumpPath + '\' + @dbDumpTemp + ' ' + @dbDumpName EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END SELECT @CMD = 'dir ' + @loadPath + '\' + @dbDumpName EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'ren ' + @loadPath + '\' + @dbDumpName + ' ' + @dbDumpTemp EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END SELECT @CMD = 'dir ' + @loadPath + '\*.DAT' EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'del ' + @loadPath + '\*.DAT' EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END SELECT @CMD = 'dir ' + @loadPath + '\' + @dbDumpTemp EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus = 0) -- found BEGIN SELECT @CMD = 'ren ' + @loadPath + '\' + @dbDumpTemp + ' ' + @dbDumpName EXEC @localCMDstatus = dispatch @CMD, @myName, @debug IF (@localCMDstatus <> 0) RETURN @localCMDstatus END END END END ELSE -- found this file in the dump archive folder SELECT @dumpSeq = @dumpSeq + 1 -- try next name IF @dumpSeq > 254 BEGIN SELECT @CMD = "Unable to create a unique log dump name. Cannot continue. (" + @myName + ")" EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus = 0) RETURN @CMDstatus RETURN 99 END END -- get the next dump file name RETURN GO /****** Object: Stored Procedure dbo.verify_sync_folders Script Date: 11/4/98 1:05:43 PM ******/ CREATE PROCEDURE verify_sync_folders @dbName VARCHAR(30) = ' ', @devicePath VARCHAR(50) = ' ', @deviceShare VARCHAR(80) = ' ', @deviceFolder VARCHAR(30) = ' ', @archive VARCHAR(80) = ' ', @serverName VARCHAR(30) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @myName VARCHAR(50), @CMD VARCHAR(255), @CMDstatus INT IF (@debug='true') BEGIN SELECT "verify_sync_folders INPUT PARMS:" SELECT "dbName: ",@dbName SELECT "devicePath: ",@devicePath SELECT "deviceShare: ",@deviceShare SELECT "deviceFolder: ",@deviceFolder SELECT "archive: ",@archive SELECT "serverName: ",@serverName END IF (@dbName = ' ') OR (@devicePath = ' ') OR (@deviceShare = ' ') OR (@deviceFolder = ' ') OR (@archive = ' ') OR (@serverName = ' ') RETURN 1 SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @CMD = 'dir ' + @devicePath EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) -- failed BEGIN SELECT @CMD = 'mkdir ' + @devicePath + '\' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* move a pkzip and pkunzip into the dump dump path */ SELECT @CMD = 'copy \\' + @serverName + '\' + @deviceShare + '\' + @deviceFolder + '\pkzip.exe \\' + @serverName + '\' + @deviceShare + '\' + @deviceFolder + '\' + @dbName EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus SELECT @CMD = 'copy \\' + @serverName + '\' + @deviceShare + '\' + @deviceFolder + '\pkunzip.exe \\' + @serverName + '\' + @deviceShare + '\' + @deviceFolder + '\'+ @dbName EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus END -- dump path does not exist /* make sure an archive path exists for this database */ SELECT @CMD = 'dir ' + @archive EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) -- not found BEGIN SELECT @CMD = 'mkdir ' + @archive + '\' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus END RETURN GO /****** Object: Stored Procedure dbo.zip_n_move Script Date: 11/4/98 1:05:43 PM ******/ CREATE PROCEDURE zip_n_move @dbName VARCHAR(30), -- the database name on both systems @fileName VARCHAR(30), -- the file to be moved (less extension) @source VARCHAR(30), -- the source server name @target VARCHAR(30), -- the target server name @sourcePassword VARCHAR(30), -- sa password on source @targetPassword VARCHAR(30), -- sa password on target @saPassword VARCHAR(30), -- admin database sa password @debug VARCHAR(5) -- debug flag AS DECLARE @sourceIP CHAR(15), @dumpShare VARCHAR(10), @dumpDrive CHAR(1), @dumpFolder VARCHAR(30), @dumpArchive VARCHAR(80), @targetIP CHAR(15), @loadShare VARCHAR(10), @loadDrive CHAR(1), @loadFolder VARCHAR(30), @loadArchive VARCHAR(80), @CMD VARCHAR(255), @CMDstatus INT, @drive varchar(5), @myName VARCHAR(30) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) /* load server info into work variables */ SELECT @sourceIP=IPaddr, @dumpShare=dumpShare, @dumpDrive=dumpDrive, @dumpFolder=dumpFolder FROM servers WHERE name = @source SELECT @targetIP = IPaddr, @loadShare=loadShare, @loadDrive=loadDrive, @loadFolder=loadFolder FROM servers where name = @target /* build some IP based UNS paths for network copy calls */ SELECT @dumpArchive = '\\' + RTRIM(@sourceIP) + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName + '\archive' SELECT @loadArchive = '\\' + RTRIM(@targetIP) + '\' + @loadShare + '\' + @loadFolder + '\' + @dbName + '\archive' IF @debug = 'true' BEGIN SELECT "dbName: ",@dbName SELECT "fileName: ", @fileName SELECT "source: ", @source SELECT "sourcePassword: ", @sourcePassword SELECT "dumpShare: ", @dumpShare SELECT "dumpDrive: ", @dumpDrive SELECT "dumpFolder: ", @dumpFolder SELECT "target: ", @target SELECT "targetPassword: ", @targetPassword SELECT "loadShare: ", @loadShare SELECT "loadDrive: ", @loadDrive SELECT "loadFolder: ", @loadFolder SELECT "saPassword: ", @saPassword END /* zip the dump file into the source system archive folder */ IF @fileName = @dbName SELECT @CMD = @dumpDrive + ':&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' + 'archive\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' + SUBSTRING(@fileName,1,4) + 'dump.dat -b' + @dumpDrive ELSE SELECT @CMD = @dumpDrive + ':&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' + 'archive\' + @fileName + '.zip ' + @fileName + '.dat -b' + @drive SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* copy the zip file to the target system */ IF @fileName = @dbName SELECT @CMD = 'copy ' + @dumpArchive + '\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' + @loadArchive + '\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' ELSE SELECT @CMD = 'copy ' + @dumpArchive + '\' + @fileName + '.zip ' + @loadArchive + '\' + @fileName + '.zip' If @debug = 'true' select @CMD EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* unzip the dump file into the target system restore folder */ IF @fileName = @dbName SELECT @CMD = @loaddrive + ':&cd ' + @loadfolder + '\' + @dbName + '&pkunzip -o ' + 'archive\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' ELSE SELECT @CMD = @loadDrive + ':&cd ' + @loadfolder + '\' + @dbName + '&pkunzip -o ' + 'archive\' + @fileName + '.zip' SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus RETURN GO /****** Object: Stored Procedure dbo.check_ident Script Date: 11/4/98 1:05:43 PM ******/ CREATE PROCEDURE check_ident @dbName VARCHAR(30) = NULL, @saPassword VARCHAR(20) = ' ' AS DECLARE @dbid INT, @lastdbid INT, @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(50) SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @saPassword = ' ' IF @dbName IS NULL -- do em all BEGIN SELECT @dbid = 1 SELECT @lastdbid = 5 -- skip the system dbs END ELSE SELECT @dbid = DB_ID(@dbName) WHILE @dbid IS NOT NULL BEGIN IF @dbName IS NULL -- do em all SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases WHERE dbid > @lastdbid) SELECT @CMD = "del C:\check_ident_" + DB_NAME(@dbid) + ".*" EXEC @CMDstatus = dispatch @CMD, @myName IF (@CMDstatus <> 0) return @CMDstatus EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader SELECT count(*) FROM textreader WHERE 1=2 INSERT textreader VALUES("USE " + DB_NAME(@dbid)) INSERT textreader SELECT line FROM scripts WHERE name = 'fix_all_ident' ORDER BY ID SELECT @CMD = 'bcp admin..textreader out ' + 'C:\check_ident_' + DB_NAME(@dbid) + '.sql -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName TRUNCATE TABLE textreader IF (@CMDstatus <> 0) return @CMDstatus EXEC release_semaphore 'textreader' SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + ' -dmaster -i"C:\check_ident_' + DB_NAME(@dbid) + '.sql"' + ' >> C:\\check_ident_' + DB_NAME(@dbid) + '.out -n' EXEC @CMDstatus = dispatch @CMD, @myName IF (@CMDstatus <> 0) return @CMDstatus IF @dbName IS NULL SELECT @lastdbid = @dbid ELSE SELECT @dbid = NULL END GO /****** Object: Stored Procedure dbo.check_outfiles Script Date: 11/4/98 1:05:44 PM ******/ CREATE PROCEDURE check_outfiles @dbName VARCHAR(30) = ' ', -- the database name @source VARCHAR(30) = ' ', -- SQL Server name @path VARCHAR(50) = ' ', -- fully qualified output path @mailto VARCHAR(30), -- valid account in same Exchange Server @saPassword VARCHAR(30), -- sa password for admin server @debug VARCHAR(5) -- debug codependant AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @mailSubject VARCHAR(255), @myName VARCHAR(50), -- this stored procedure @outFile VARCHAR(80) -- working var for qualified file name /* Procedure check_outfiles looks for errors in the out files by parsing the maintenance output files for specific words associated with problems in the database. Designated user(s) are notified by email only if problems are found */ IF (@dbName = ' ') OR (@source = ' ') OR (@path = ' ') RETURN 1 SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) DELETE messenger where spid = @@SPID SELECT @outfile = @path + '\ck_outfiles.results' SELECT @CMD = 'del ' + @outfile EXEC @CMDstatus = dispatch @CMD, @myName, @debug SELECT @CMD = 'echo The maintenance output files have been reviewed for problems.>' + @outfile EXEC @CMDstatus = dispatch @CMD, @myName, @debug SELECT @CMD = 'echo Review all files listed below and resolve all exceptions>>' + @outfile EXEC @CMDstatus = dispatch @CMD, @myName, @debug SELECT @CMD = 'findstr "dbprocess Level error:" ' + @path + '\*.out>>' + @outfile EXEC @CMDstatus = master..xp_cmdshell @CMD /* put an explaination header in the messenger file */ INSERT messenger SELECT @@SPID, '- DATABASE: ' + @dbName INSERT messenger SELECT @@SPID, '- SOURCE SYSTEM: ' + @source INSERT messenger SELECT @@SPID, '- OUTFILE PATH: ' + @path EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @path + '\ck_outfiles.results -c -Usa -P' + @saPassword + ' -S' + @source EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (select count(*) from textreader) > 2 BEGIN INSERT messenger SELECT @@SPID, text FROM textreader SELECT @mailSubject = 'Maintenance problems identified! database: ' + @dbName + ' server: ' + @source TRUNCATE TABLE textreader DELETE messenger where spid = @@SPID SELECT @CMD = 'SELECT info FROM admin.dbo.messenger WHERE info ' + 'IS NOT NULL AND spid = ' + CONVERT(VARCHAR(10), @@SPID) EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @query = @CMD END TRUNCATE TABLE textreader EXEC release_semaphore 'textreader' DELETE messenger where spid = @@SPID RETURN GO /****** Object: Stored Procedure dbo.CheckPULIST Script Date: 11/4/98 1:05:44 PM ******/ CREATE PROCEDURE CheckPULIST @server VARCHAR(30) = ' ', @sendTo VARCHAR(255) = '/USERS', -- domain user(s) or computer name(s) @saPassword VARCHAR(30) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @found INT, -- number of Dr Watsons found @singleSendTo VARCHAR(15), -- working var if multiple send tos @myName VARCHAR(50) -- name of this function /* Check processes on web server. If Dr Watson is running, tell somebody. */ SET NOCOUNT ON IF (@server = ' ') SELECT @server = @@SERVERNAME SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) /* load the pulist into a table and scan for Dr Watson */ EXEC hold_semaphore 'PULISTtext' TRUNCATE TABLE PULISTtext /* run a pulist to get active processes on server */ SELECT @CMD = "cd c:\ntreskit&pulist " + @server INSERT PULISTtext EXEC master..xp_cmdshell @CMD SELECT @found= (SELECT COUNT(*) FROM PULISTtext WHERE text like '%DRWTSN32%') TRUNCATE TABLE PULISTtext EXEC release_semaphore 'PULISTtext' /* send message if Dr Watson found */ IF (@found <> 0) BEGIN IF (CHARINDEX(" ", @sendto)>0) BEGIN WHILE (CHARINDEX(" ",@sendto) > 0) BEGIN SELECT @singleSendTo = SUBSTRING(@sendto, 1, CHARINDEX(" ", @sendto) - 1) SELECT @sendto = SUBSTRING(@sendto, CHARINDEX(" ", @sendto) + 1, DATALENGTH(@sendto) - (CHARINDEX(" ", @sendto))) SELECT @CMD = "net send " + @singleSendto + " Dr Watson is running on " + @server + " !" EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END END /* get the last one or only one */ SELECT @CMD = "net send " + @sendto + " Dr Watson is running on " + @server + " !" EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END GO /****** Object: Stored Procedure dbo.convert_share_to_drive Script Date: 11/4/98 1:05:44 PM ******/ CREATE PROCEDURE convert_share_to_drive @share VARCHAR(30) = ' ', @drive VARCHAR(5) = ' ' OUTPUT, @path VARCHAR(50) = ' ', @source VARCHAR(30) = ' ', @sourcePassword VARCHAR(30) = ' ', @saPassword VARCHAR(30) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(50) /* Procedure convert_share_to_drive will return a logical drive name for a valid network share name. */ IF (@share = ' ') OR (@path = ' ') OR (@source = ' ') RETURN 1 SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"master..xp_cmdshell ' + "'net share " + @share + ">" + @path + "\convert.share'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @path + '\convert.share -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus = 0) BEGIN IF (@debug='true') BEGIN SELECT "net use info" SELECT * from textreader END SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"master..xp_cmdshell ' + "'del " + @path + "\convert.share'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug SELECT @drive = SUBSTRING((SELECT DISTINCT RTRIM(LTRIM(SUBSTRING(text, CHARINDEX('path',text) + DATALENGTH('path'), DATALENGTH(text)))) FROM textreader WHERE text LIKE 'PATH%'),1,2) END IF (@debug='true') BEGIN SELECT RTRIM(@myName) + " DEBUG INFO! -->" + " share = " + @share + " -->" + " drive = " + @drive + " -->" + " path = " + @path + " -->" + " source = " + @source + " -->" + " sourcePassword = " + @sourcePassword + " -->" + " saPassword = " + @saPassword + " --> END" SELECT * from textreader END TRUNCATE TABLE textreader EXEC release_semaphore 'textreader' RETURN @CMDstatus GO /****** Object: Stored Procedure dbo.expunge_users Script Date: 11/4/98 1:05:45 PM ******/ CREATE PROCEDURE expunge_users @dbName VARCHAR(30) = ' ', @target VARCHAR(30) = ' ', @targetPassword VARCHAR(30) = ' ', @saPassword VARCHAR(30) = ' ', @path VARCHAR(50) = ' ', @debug VARCHAR(5) = 'false' AS DECLARE @bail SMALLINT, @CMD VARCHAR(255), @CMDstatus INT, @myName VARCHAR(50), @spidToKill VARCHAR(5) IF (@dbName <> ' ') AND (@target <> ' ') AND (@path <> ' ') BEGIN SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @bail = 10 WHILE (@bail > 0) BEGIN DELETE messenger where spid = @@SPID SELECT @CMD = "SELECT max(spid) FROM master..sysprocesses " + "WHERE dbid = (SELECT dbid FROM master..sysdatabases " + "WHERE name = '" + @dbName + "')" SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + @CMD + '" -o' + @path + '\userCount.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @path + '\userCount.txt -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus INSERT messenger SELECT @@SPID, text from textreader TRUNCATE TABLE textreader SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"master..xp_cmdshell ' + "'del " + @path + "\userCount.txt'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus INSERT messenger SELECT @@SPID, RTRIM(LTRIM(text)) FROM textreader TRUNCATE TABLE textreader EXEC release_semaphore 'textreader' SELECT @spidToKill = (SELECT min(info) FROM messenger WHERE spid = @@SPID AND info <> " " AND info NOT LIKE "---%" AND info NOT LIKE "%affected)%") IF (SELECT ISNUMERIC(@spidToKill))= 0 SELECT @bail = -1 ELSE BEGIN IF (@target = @@SERVERNAME) AND (CONVERT(VARCHAR(5), @@SPID) = @spidToKill) BEGIN SELECT @CMD = 'Cannot load database ' + @dbName + ' on ' + @target + '. This process is running in the database.' EXEC @CMDstatus = dispatch @CMD, @myName, @debug RETURN @CMDStatus END SELECT @CMD = "kill " + @spidToKill SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus IF @bail = 0 BEGIN SELECT @CMD = 'There are active users in ' + @dbName + ' on ' + @target + ' that cannot be disconnected at this time.' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END SELECT @bail = @bail - 1 END END DELETE messenger where spid = @@SPID RETURN END ELSE BEGIN PRINT " usage: expunge_users [@dbName = ] ," PRINT " [@target = ] ," PRINT " [@targetPassword = ] ," PRINT " [@saPassword = ] ," PRINT " [@path = ] ," PRINT " [@debug = ] ," RETURN 1 END GO /****** Object: Stored Procedure dbo.run_script_remote Script Date: 11/4/98 1:05:45 PM ******/ CREATE PROCEDURE run_script_remote @name VARCHAR(30) = ' ', @dbNAme VARCHAR(30) = ' ', @source VARCHAR(30) = ' ', @sourcePassword VARCHAR(30) = ' ', @path VARCHAR(50) = ' ', @saPassword VARCHAR(30) = ' ', @ext VARCHAR(3) = 'out', @debug VARCHAR(5) = 'false' AS DECLARE @scriptName VARCHAR(30), @myName VARCHAR(50), @scriptFile VARCHAR(35), @outFile VARCHAR(35), @CMD VARCHAR(255), @CMDstatus INT /* Procedure run_remote_script will run any script in the admin subsystem's scripts table that is prefixed with 'script_' on any server with an adequate trust relationship to the admin SQL Server. The scripts table nameing convention is assumed. */ IF (@name = ' ') OR (@dbName = ' ') OR (@source = ' ') OR (@path NOT LIKE '\\%') RETURN 1 SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @scriptName = 'script_' + @name SELECT @scriptFile = '\' + @name + '.sql' SELECT @outFile = '\' + @name + '.' + @ext /* move the script to flat file in a folder with read/write permissions granted to the login account of the admin SQL Server and the source SQL Server */ EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader INSERT textreader SELECT line FROM scripts WHERE name = @scriptName ORDER BY ID IF @debug = 'true' SELECT * FROM textreader SELECT @CMD = 'bcp admin..textreader out ' + @path + @scriptFile + ' -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug TRUNCATE TABLE textreader IF (@CMDstatus <> 0) return @CMDstatus EXEC release_semaphore 'textreader' /* run the script on the source SQL Server */ IF @ext = ' ' SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -i"' + @path + @scriptFile + '" -n' ELSE SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -i"' + @path + @scriptFile + '" -o' + @path + @outFile + " -n" EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* delete the script */ SELECT @CMD = 'del ' + @path + @scriptFile EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus RETURN GO /****** Object: Stored Procedure dbo.verify_sysusages Script Date: 11/4/98 1:05:45 PM ******/ CREATE PROCEDURE verify_sysusages @dbName VARCHAR(30) = ' ', -- the database name on both systems @source VARCHAR(30) = ' ', -- the source server name @sourcePassword VARCHAR(30) = ' ', -- sa password on source @target VARCHAR(30) = ' ', -- the target server name @targetPassword VARCHAR(30) = ' ', -- sa password on target @saPassword VARCHAR(30) = ' ', -- sa password on admin server @loadPath VARCHAR(50) = ' ', -- fully qualified load path @mailTo VARCHAR(30) = ' ', -- address to mail debug info @debug VARCHAR(5) = 'false' -- debug this procedure AS /* verify_sysusages compares the size allocated in master..sysusages for two databases and reports a problem if they are not the same size */ DECLARE @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @myName VARCHAR(50) -- name of this function SET NOCOUNT ON SELECT @mailto = emailAddress FROM persons WHERE role='DBA' SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) IF (@dbName <> ' ') AND (@source <> ' ') AND (@target <> ' ') AND (@loadPath <> ' ') BEGIN SELECT @CMD = "SELECT SUM(size) FROM master..sysusages WHERE dbid = DB_ID('" + @dbName + "')" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -Q"' + @CMD + '" -o' + @loadpath + '\' + @source + @dbName + '.siz' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = "SELECT SUM(size) FROM master..sysusages WHERE dbid = DB_ID('" + @dbName + "')" SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -Q"' + @CMD + '" -o' + @loadpath + '\' + @target + @dbName + '.siz' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus DELETE messenger WHERE spid = @@SPID EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + '\' + @source + @dbName + '.siz -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + '\' + @target + @dbName + '.siz -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus INSERT messenger SELECT @@SPID, text FROM textreader WHERE text NOT LIKE " -%" AND text NOT LIKE "(1 %" AND text NOT LIKE " " AND text IS NOT NULL TRUNCATE TABLE textreader EXEC release_semaphore 'textreader' /* do some cleanup */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"master..xp_cmdshell ' + "'del " + @loadPath + "\*.siz'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus IF EXISTS (SELECT info FROM messenger WHERE spid = @@SPID AND ISNUMERIC(info) = 0) BEGIN SELECT @CMD = 'An error occurred while checking the size of database ' + @dbName + ' on ' + @source + ' and ' + @target + '. (' + @myName + ')' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) BEGIN SELECT @CMD = "SELECT * FROM admin.dbo.messenger WHERE spid = " + CONVERT(VARCHAR(5),@@SPID) EXEC master..xp_sendmail @mailTo, 'admin.verify_sysusages error info', @query = @CMD RETURN @CMDstatus END DELETE messenger where spid = @@SPID RETURN 99 END IF NOT EXISTS (SELECT * FROM messenger WHERE spid = @@SPID GROUP BY info HAVING COUNT(*) > 1) BEGIN IF @debug = 'true' BEGIN SELECT @CMD = "SELECT * FROM admin.dbo.messenger WHERE spid = " + CONVERT(VARCHAR(5),@@SPID) EXEC master..xp_sendmail @mailTo, 'admin.verify_sysusages debug info', @query = @CMD END SELECT @CMD = 'The ' + @source + ' and ' + @target + ' ' + @dbName + ' databases are not the same size. (' + @myName + ')' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus DELETE messenger where spid = @@SPID RETURN 99 END DELETE messenger WHERE spid = @@SPID RETURN END ELSE BEGIN PRINT " usage: verify_sysusages" PRINT " [@dbname = ]," PRINT " [@source = ]," PRINT " [@sourcePassword = ]," PRINT " [@target = ]," PRINT " [@targetPassword = ]," PRINT " [@saPassword = ]," PRINT " [@loadPath = ]," PRINT " [@debug = ]," RETURN 1 END GO /****** Object: Stored Procedure dbo.logSync Script Date: 11/4/98 1:05:45 PM ******/ CREATE PROCEDURE logSync @dbName VARCHAR(30) = ' ', -- the database name on both systems @source VARCHAR(30) = ' ', -- the source server name @target VARCHAR(30) = ' ', -- the target server name @sourcePassword VARCHAR(30) = ' ', -- sa password on source @targetPassword VARCHAR(30) = ' ', -- sa password on target @saPassword VARCHAR(30) = ' ', -- sa password on admin server @debug VARCHAR(5) = 'false' AS DECLARE @candidateTable VARCHAR(30), -- for verification of restore (rowcount) @dumpShare VARCHAR(10), -- drive share name where dump folder resides @dumpDrive VARCHAR(5), -- drive logical name where dump folder resides @dumpFolder VARCHAR(30), -- source sys top level (full path less drive) @dumpPath VARCHAR(50), -- fully qualified dump path @dumpDevice VARCHAR(80), -- fully qualified dump device @dumpArchive VARCHAR(80), -- source archive PATH @loadShare VARCHAR(10), -- drive share name where load folder resides @loadDrive VARCHAR(5), -- drive logical name where load folder resides @loadFolder VARCHAR(30), -- target sys top level (full path less drive) @loadPath VARCHAR(50), -- fully qualified load path @loadDevice VARCHAR(80), -- fully qualified load device @loadArchive VARCHAR(80), -- target archive PATH @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @mailto VARCHAR(30), -- valid account in same Exchange Server @mailSubject VARCHAR(80), -- mail subject string @mailAttachment VARCHAR(255), -- mail subject string @fileName VARCHAR(30), -- name of this log dump @myName VARCHAR(30), -- this procedures name @task VARCHAR(100) -- working var to invoke dbSync on fail /* logSync will dump a database and restore the dump to a hot backup destination. Both the source and target databases will be configured to support an async log dump and restore process. All dumps will be archived for the source and target systems. */ SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) /* verify some parms - exit if inconsistent */ IF (@dbName = ' ') OR (@candidateTable = ' ') OR (@source = ' ') OR (@target = ' ') RETURN 1 /* if system db display error message and exit */ IF DB_ID(@dbName) < 6 BEGIN SELECT @CMD = @myName + ' cannot be used on system database ' + @dbName + '. Only user databases can be safely moved via dump/load.' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* if debug is on, submit job to batch and produce debug output info */ IF (@debug="true") AND NOT EXISTS (SELECT program_name FROM master..sysprocesses WHERE spid = @@SPID AND program_name LIKE "ISQL%") BEGIN /* load server info into work variables */ SELECT @dumpShare=dumpShare, @dumpFolder=dumpFolder FROM servers WHERE name = @source SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + ' -d' + DB_NAME() + ' -Q"' + OBJECT_NAME(@@PROCID) + " '" + + @dbName + "','" + + @source + "','" + + @target + "','" + + @sourcePassword + "','" + + @targetPassword + "','" + + @saPassword + "','" + + @debug + "'" + + '" -o' + '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName + '\' + OBJECT_NAME(@@PROCID) + 'debug.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF @CMDstatus <> 0 BEGIN SELECT @CMD = 'Attempt to submit the ' + @myName + ' process for database ' + @dbName + + ' from server ' + @source + + ' to server ' + @target + + ' in debug mode has failed!' RAISERROR(@CMD,1,1) WITH SETERROR END END ELSE BEGIN /* make sure the db is available on source and target else exit*//* SELECT @CMDstatus = 0 INSERT in_use VALUES (@dbName, @source, OBJECT_NAME(@@PROCID), getdate()) IF @@ERROR <> 0 SELECT @CMDstatus = 1 INSERT in_use VALUES (@dbName, @target, OBJECT_NAME(@@PROCID), getdate()) IF @@ERROR <> 0 SELECT @CMDstatus = @CMDstatus + 2 IF @CMDstatus <> 0 BEGIN SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " + CONVERT(VARCHAR(5), @CMDstatus) + ")" IF (@CMDstatus = 1) SELECT @CMD = 'An adminstrative subsystem process is already ' + 'active in database ' + @dbName + ' on server ' + @source + '. Only one admin process may be active ' + 'in any database at any time.(' + @myName + ')' IF (@CMDstatus = 2) SELECT @CMD = 'An adminstrative subsystem process is already ' + 'active in database ' + @dbName + ' on server ' + @target + '. Only one admin process may be active ' + 'in any database at any time.(' + @myName + ')' IF (@CMDstatus = 3) SELECT @CMD = 'An adminstrative subsystem process is already ' + 'active in database ' + @dbName + ' on server ' + @source + ' and server ' + @target + '. Only one admin process may be active ' + 'in any database at any time.(' + @myName + ')' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END -- in use */ /* load server info into work variables */ SELECT @mailto=emailAddress FROM persons WHERE role="DBA" SELECT @dumpShare=dumpShare, @dumpDrive=dumpDrive, @dumpFolder=dumpFolder FROM servers WHERE name = @source SELECT @loadShare=loadShare, @loadDrive=loadDrive, @loadFolder=loadFolder FROM servers WHERE name = @target SELECT @candidateTable=compareTableName FROM databases where name=@dbName SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName SELECT @dumpArchive = @dumpPath + '\archive' SELECT @loadPath = '\\' + @target + '\' + @loadShare + '\' + @loadFolder + '\' + @dbName SELECT @loadArchive = @loadPath + '\archive' /* make sure no one else is in the target database else load will fail */ EXEC @CMDstatus = admin.dbo.expunge_users @dbName, @target, @targetPassword, @saPassword, @loadPath, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* make sure the source system dump device path is valid */ SELECT @CMD = 'dir ' + @dumpPath EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus <> 0) RETURN @CMDstatus /* make sure a source system archive path exists for this database */ SELECT @CMD = 'dir ' + @dumpArchive EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus <> 0) RETURN @CMDstatus /* make sure a target system archive path exists for this database */ SELECT @CMD = 'dir ' + @loadArchive EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus <> 0) RETURN @CMDstatus /* find a name for the logdump. If this is the first log dump for the db today, cleanup all previous log dumps */ EXEC @CMDstatus = setLogDump @dbName, -- database name @dumpPath, -- fully qualified dump path @loadPath, -- fully qualified load path @fileName OUTPUT, -- name to use @debug SELECT @dumpDevice = @dumpPath + '\' + @fileName SELECT @loadDevice = @loadPath + '\' + @fileName IF (@debug = 'true') BEGIN SELECT '@filename: ',@fileName SELECT '@dumpDevice: ',@dumpDevice SELECT '@loadDevice: ',@loadDevice END /* count the number of rows in the candidate table in the source database */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"select count(*) as ' + @source + ' from ' + @candidateTable + '" -o' + @loadPath + '\sourceRows.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* IF @debug = 'true' BEGIN SELECT @mailSubject = 'DEBUG INFO! ' + @dbName + ' logSync source rowcount results' SELECT @mailAttachment = @loadPath + '\sourceRows.txt' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @message = @cmd, @attachments = @mailAttachment END */ /* strip the ext off of the log dump file name */ SELECT @filename = SUBSTRING(@fileName,1,CHARINDEX(".",@fileName) - 1) /* dump the database on the source system */ SELECT @CMD = 'isql -Usa -P -S' + @source + ' -dmaster -Q"DUMP TRANSACTION ' + @dbName + " TO DISK = '" + RTRIM(LTRIM(@dumpdevice)) + "'" + '" -o' + @dumpPath + '\' + @fileName + '.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus EXEC verify_dump @source, @dumpDevice, @CMDstatus OUTPUT, @debug If (@CMDstatus = 0) BEGIN SELECT @CMD = @dumpPath + '\' + @fileName + '.out' SELECT @mailSubject = 'Unable to verify ' + @dbName + ' log dump!' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @attachments = @CMD SELECT @CMD = 'Unable to verify log dump to ' + @dumpDevice + ' of database ' + @dbName + ' on server ' + @source + '. The ' + @myName + ' process has been aborted.' RAISERROR(@CMD,1,1) WITH SETERROR /* make the db available on source and target */ DELETE in_use WHERE dbName = @dbName AND server in (@source, @target) RETURN 1 END If (@CMDstatus > 1) BEGIN SELECT @CMD = 'Multiple log dumps of database ' + @dbName + ' on server ' + @source + ' to device ' + @dumpDevice + ' have occurred in the last 30 minutes. The ' + @myName + ' process will continue. Investigate this incident ASAP.' RAISERROR(@CMD,1,1) WITH SETERROR END /* zip the dump into the archive and move it to the hot site */ EXEC @CMDstatus = zip_n_move @dbName, @filename, @source, @target, @sourcePassword, @targetPassword, @saPassword, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* configure the target database for load of source dump */ EXEC @CMDstatus = set_to_load @dbName, @target, @targetPassword, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* restore the dump to the target system */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"LOAD TRANSACTION ' + @dbName + " FROM DISK = '" + RTRIM(LTRIM(@loadDevice)) + "'" + '" -o' + @loadPath + '\' + @fileName + '.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus EXEC verify_load @target, @loadDevice, @CMDstatus OUTPUT, @debug If (@CMDstatus = 0) BEGIN /* make the db available on source and target */ DELETE in_use WHERE dbName = @dbName AND server in (@source, @target) /* notify DBA of failure */ SELECT @CMD = @loadPath + '\' + @fileName + '.out' SELECT @mailSubject = @dbName + ' logSync Load failed. dbSync has been initiated' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @attachments = @CMD /* submit a dbSync task */ SELECT @task = 'dbSync ' + @dbName + ' ' + @source + ' to ' + @target EXEC msdb..sp_runtask @task RETURN 1 END ELSE BEGIN /* configure the target database as the hot site */ EXEC @CMDstatus = set_to_hot_site @dbName, @target, @targetPassword, @debug IF (@CMDstatus <> 0) return @CMDstatus /* check candidate table rowcount from source and target systems send email report if the rowcount is different */ SELECT @CMD = 'isql -Usa -P -S' + @target + ' -d' + @dbName + ' -Q"select count(*) as ' + @target + ' from ' + @candidateTable + '" -o' + @loadPath + '\targetRows.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* IF @debug = 'true' BEGIN SELECT @mailSubject = 'DEBUG INFO! ' + @dbName + ' logSync target db rowcount results' SELECT @mailAttachment = @loadPath + '\targetRows.txt' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @message = @cmd, @attachments = @mailAttachment END */ /* put an explaination header in the messenger file */ DELETE messenger where spid = @@SPID INSERT messenger SELECT @@SPID, 'Count of the rows in a candidate table on the source' INSERT messenger SELECT @@SPID, 'system just before the log is dumped and the same table' INSERT messenger SELECT @@SPID, 'on the target system just after the log is loaded.' INSERT messenger SELECT @@SPID, '- CANDIDATE TABLE: ' + @candidateTable INSERT messenger SELECT @@SPID, '- DATABASE: ' + @dbName INSERT messenger SELECT @@SPID, '- SOURCE SYSTEM: ' + @source INSERT messenger SELECT @@SPID, '- TARGET SYSTEM: ' + @target INSERT messenger SELECT @@SPID, '***** source and target row count should be equal *****' INSERT messenger SELECT @@SPID, '.' EXEC hold_semaphore 'textreader' TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + '\sourceRows.txt -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug INSERT messenger SELECT @@SPID, text FROM textreader TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + '\targetRows.txt -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug INSERT messenger SELECT @@SPID, text FROM textreader SELECT @CMD = 'SELECT info FROM admin.dbo.messenger WHERE info ' + 'IS NOT NULL AND spid = ' + CONVERT(VARCHAR(10), @@SPID) UPDATE messenger SET info = " " WHERE info LIKE "(1 row %" AND spid = @@SPID UPDATE messenger SET info = rtrim(ltrim(info)) WHERE spid = @@SPID IF (SELECT info FROM messenger WHERE ID = 13) <> (SELECT info FROM messenger WHERE ID = 16) BEGIN SELECT @mailSubject = @dbName + ' ' + @myName + ' consistency check discrepency report' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @query = @CMD END DELETE messenger where spid = @@SPID TRUNCATE TABLE textreader EXEC release_semaphore 'textreader' /* clean up consistency check files */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"master..xp_cmdshell ' + "'del " + @loadPath + "\*Rows.txt'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus END END -- debug in batch RETURN GO /****** Object: Stored Procedure dbo.maintenance Script Date: 11/4/98 1:05:45 PM ******/ CREATE PROCEDURE maintenance @dbName VARCHAR(30) = ' ', -- the database name @source VARCHAR(30) = ' ', -- the server name @dumpMe VARCHAR(5) = 'true', -- flag to enable dump processing @sourcePassword VARCHAR(30) = ' ', -- sa password on source @saPassword VARCHAR(30) = ' ', -- sa password for admin server @debug VARCHAR(5) = 'false' -- debug codependant AS DECLARE @dumpPath VARCHAR(50), -- dump path @dumpDevice VARCHAR(80), -- fully qualified dump device @dumpArchive VARCHAR(80), -- source archive PATH @dumpShare VARCHAR(30), -- source system drive share name @drive VARCHAR(5), -- source system drive logical name @dumpFolder VARCHAR(30), -- source system top level directory @CMD VARCHAR(255), -- reusable command holder @CMDstatus INT, -- return status of command prompt @mailto VARCHAR(30), -- DBAs email address @mailSubject VARCHAR(80), -- mail subject string @mailMessage VARCHAR(80), -- mail message string @myName VARCHAR(50) -- this stored procedure SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID) SELECT @mailto=emailAddress FROM persons WHERE role="DBA" /* Procedure will perform maintenance and dump of a database. The previous dump will be kept in a zipped archive along with the current dump. */ IF (@dbName = ' ') OR (@source = ' ') BEGIN EXEC sp_help maintenance RETURN 1 END /* if debug is on and the job is not already in batch, submit job to batch and produce debug output info */ IF (@debug="true") AND NOT EXISTS (SELECT program_name FROM master..sysprocesses WHERE spid = @@SPID AND program_name LIKE "ISQL%") BEGIN /* load server info into work variables */ SELECT @dumpShare=dumpShare, @dumpFolder=dumpFolder FROM servers WHERE name = @source SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName SELECT @dumpArchive = @dumpPath + '\archive' /* verify necessary file systems for target server */ EXEC @CMDstatus = verify_sync_folders @dbName, @dumpPath, @dumpShare, @dumpFolder, @dumpArchive, @source, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + ' -d' + DB_NAME() + ' -Q"' + OBJECT_NAME(@@PROCID) + " '" + + @dbName + "','" + + @source + "','" + + @dumpMe + "','" + + @sourcePassword + "','" + + @saPassword + "','" + + @debug + "'" + + '" -o' + '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName + '\' + OBJECT_NAME(@@PROCID) + 'debug.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF @CMDstatus <> 0 BEGIN SELECT @CMD = 'Attempt to submit the ' + @myName + ' process for database ' + @dbName + + ' from server ' + @source + + ' in debug mode has failed!' RAISERROR(@CMD,1,1) WITH SETERROR END END ELSE BEGIN /* load server info into work variables */ IF (@dumpMe='true') SELECT @dumpShare=dumpShare, @drive=dumpDrive, @dumpFolder=dumpFolder FROM servers WHERE name = @source ELSE SELECT @dumpShare=loadShare, @drive=loadDrive, @dumpFolder=loadFolder FROM servers WHERE name = @source /* build file system paths used by this the maintenance procedure */ SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName SELECT @dumpDevice = @dumpPath + '\' + SUBSTRING(@dbName,1,4) +"dump.DAT" SELECT @dumpArchive = @dumpPath + '\archive' IF @debug = 'true' SELECT "maintenance DEBUG info! ==> @myName=", @myName, " ==> dumpPath=", @dumpPath, " ==> dumpDevice=", @dumpDevice " ==> dumpArchive=", @dumpArchive /* verify necessary file systems for target server */ EXEC @CMDstatus = verify_sync_folders @dbName, @dumpPath, @dumpShare, @dumpFolder, @dumpArchive, @source, @debug IF (@CMDstatus <> 0) return @CMDstatus /* archive the maintenance output files */ SELECT @CMD = 'dir ' + @dumpPath + '\*.out' EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus = 0) -- found BEGIN SELECT @CMD = 'dir ' + @dumpPath + '\*.001' EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output IF (@CMDstatus = 0) -- found BEGIN SELECT @CMD = 'del ' + @dumpPath + '\*.001' EXEC @CMDstatus = dispatch @CMD, @myName, @debug END SELECT @CMD = 'ren ' + @dumpPath + '\*.out '+ '*.001' EXEC @CMDstatus = dispatch @CMD, @myName, @debug END /**************** MAINTENANCE PROCESSING *********************/ /* build scripts to set permissions as they now exist */ EXEC run_script_remote "permissions", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /* build scripts to set users and alias as they now exist */ EXEC run_script_remote "users", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /* build scripts to set row level locks as they now exist */ EXEC run_script_remote "row_level_locks", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /* Check the table allocation chain (capture elapsed time in outfile) */ SELECT @CMD = 'isql -Usa -e -p -P'+ @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"DBCC CHECKDB" -o' + @dumpPath + '\checkdb.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* Check the storage allocation chain (capture elapsed time in outfile)*/ SELECT @CMD = 'isql -Usa -e -p -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"DBCC NEWALLOC" -o' + @dumpPath + '\newalloc.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* Check the system tables allocation chain */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"DBCC CHECKCATALOG" -o' + @dumpPath + '\checkcatalog.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* get the sp_help info for the database */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"sp_helpdb ' + @dbName + '" -o' + @dumpPath + '\helpdb.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* check the space available in the database notify somebody if its getting full*/ EXEC run_script_remote "check_space", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /************* MSDB ONLY *************/ IF @dbName = 'msdb' BEGIN /* get systasks info */ EXEC run_script_remote "tasks", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /* clean up task history tables */ SELECT @CMD = "admin..purgeMSDB @source = '" + @source + "', @sourcePassword = '" + @sourcePassword + "', @debug = '" + @debug + "'" EXEC (@CMD) END -- msdb database /************* MASTER ONLY *************/ IF @dbName = 'master' BEGIN /* get the reverse engineered database create script */ EXEC run_script_remote "devs_and_dbs", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /* build create scripts for any user extended stored procedures */ EXEC run_script_remote "user_xps", @dbName, @source, @sourcePassword, @dumpPath, @saPassword, 'out', @debug SELECT @CMDstatus = @@ERROR IF (@CMDstatus <> 0) return @CMDstatus /* get the info from sysdatabase, sysdevices, and sysusages for critical failure recovery processing */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"select * from sysdatabases" -o' + @dumpPath + '\sysdatabases.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"select * from sysdevices" -o' + @dumpPath + '\sysdevices.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"select * from sysusages" -o' + @dumpPath + '\sysusages.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* get the SQL Server configuration info */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"sp_configure" -o' + @dumpPath + '\configure.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus /* get the memory usage info */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"DBCC MEMUSAGE" -o' + @dumpPath + '\memusage.out' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END -- master database /**************** DUMP THE DATABASE ****************/ IF @dumpMe = "true" BEGIN /* cleanup the dump files */ EXEC @CMDstatus = delete_archive @dumpArchive, @debug IF (@CMDstatus <> 0) select @CMDstatus /* dump the database */ EXEC @CMDstatus = dumpDatabase @dbName, @source, @sourcePassword, @dumpFolder, @drive, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* archive the dump */ SELECT @CMD = @drive + ':&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' + 'archive\' + SUBSTRING(@dbName,1,4) + 'dump.zip ' + SUBSTRING(@dbName,1,4) + 'dump.dat -b' + @drive + ":" SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -dmaster -Q"' + @CMD + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) return @CMDstatus END /* Check for errors in the out files and mail results. Parse the maintenance output files for specific words that may indicate problems in the database. Mail the results. */ EXEC check_outfiles @dbName, @source, @dumpPath, @mailto, @saPassword, @debug END -- debug in batch RETURN GO /****** Object: Stored Procedure dbo.dbSync Script Date: 11/4/98 1:05:45 PM ******/ CREATE PROCEDURE dbSync @dbName VARCHAR(30) = ' ', -- database @source VARCHAR(30) = ' ', -- source server @target VARCHAR(30) = ' ', -- target server @sourcePassword VARCHAR(30) = ' ', -- source sa password @targetPassword VARCHAR(30) = ' ', -- target sa password @saPassword VARCHAR(30) = ' ', -- admin sa password @maintain VARCHAR(5) = 'true', -- maintain target @debug VARCHAR(5) = 'false' -- enable debug AS DECLARE @candidateTable VARCHAR(30), -- table to check @dumpShare VARCHAR(10), -- source outfile share @dumpFolder VARCHAR(30), -- source outfile path @dumpDrive VARCHAR(5), -- logical drive name of dump share @dumpPath VARCHAR(50), -- fully qualified dump path @dumpDevice VARCHAR(80), -- fully qualified dump device @dumpArchive VARCHAR(80), -- source archive path @loadShare VARCHAR(10), -- target outfile share @loadFolder VARCHAR(30), -- target outfile path @loadDrive VARCHAR(5), -- logical drive name of load share @loadPath VARCHAR(50), -- fully qualified load path @loadDevice VARCHAR(80), -- fully qualified load device @loadArchive VARCHAR(80), -- target archive path @CMD VARCHAR(255), -- command holder @CMDstatus INT, -- return status of command prompt @mailto VARCHAR(30), -- admin email address @mailSubject VARCHAR(80), @myName VARCHAR(50) -- this procedure /* Procedure dbSync will dump a database and restore the dump to a hot backup destination. Both the source and target databases will be configured to support an async log dump and restore process. All dumps will be archived for the source and target. */ SET NOCOUNT ON SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID) /* if no dbName display usage message and exit */ IF (@dbName = ' ') OR (@candidateTable = ' ') OR (@source = ' ') OR (@target = ' ') BEGIN SELECT @CMD = "Invalid parameter list supplied to procedure " + @myName RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* if system db display error message and exit */ IF DB_ID(@dbName) < 6 BEGIN SELECT @CMD = @myName + ' cannot be used on system database ' + @dbName + '. Only user databases can be safely ' + 'moved via dump/load.' RAISERROR(@CMD,1,1) WITH SETERROR RETURN 1 END /* if debug is on, submit job to batch and produce debug output info */ IF (@debug="true") AND NOT EXISTS (SELECT program_name FROM master..sysprocesses WHERE spid = @@SPID AND program_name LIKE "ISQL%") BEGIN /* load server info into work variables */ SELECT @dumpShare=dumpShare, @dumpFolder=dumpFolder FROM servers WHERE name = @source SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + ' -d' + DB_NAME() + ' -Q"' + OBJECT_NAME(@@PROCID) + " '" + + @dbName + "','" + + @source + "','" + + @target + "','" + + @sourcePassword + "','" + + @targetPassword + "','" + + @saPassword + "','" + + @maintain + "','" + + @debug + "'" + + '" -o' + '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName + '\' + OBJECT_NAME(@@PROCID) + 'debug.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF @CMDstatus <> 0 BEGIN SELECT @CMD = 'Attempt to submit the ' + @myName + ' process for database ' + @dbName + + ' from server ' + @source + + ' to server ' + @target + + ' in debug mode has failed!' RAISERROR(@CMD,1,1) WITH SETERROR END END ELSE BEGIN SELECT @mailto=emailAddress FROM persons WHERE role="DBA" SELECT @dumpShare=dumpShare, @dumpDrive=dumpDrive, @dumpFolder=dumpFolder FROM servers WHERE name = @source SELECT @loadShare=loadShare, @loadDrive=loadDrive, @loadFolder=loadFolder FROM servers WHERE name = @target SELECT @candidateTable=compareTableName FROM databases where name=@dbName /* build file system paths used by this replication process */ SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + @dumpFolder + '\' + @dbName SELECT @dumpDevice = @dumpPath + '\' + SUBSTRING(@dbName,1,4) +"dump.DAT" SELECT @dumpArchive = @dumpPath + '\archive' SELECT @loadPath = '\\' + @target + '\' + @loadShare + '\' + @loadFolder + '\' + @dbName SELECT @loadDevice = @loadPath + '\' + SUBSTRING(@dbName,1,4) + "dump.DAT" SELECT @loadArchive = @loadPath + '\archive' IF @debug = 'true' BEGIN SELECT "!!! START Local Variables DEBUG Listing !!!" SELECT " myName = ", @myName SELECT " dumpDrive = ", @dumpDrive SELECT " dumpPath = ", @dumpPath SELECT " dumpDevice = ", @dumpDevice SELECT " dumpArchive = ", @dumpArchive SELECT " loadDrive = ", @loadDrive SELECT " loadPath = ", @loadPath SELECT " loadDevice = ", @loadDevice SELECT " loadArchive = ", @loadArchive SELECT " maintain Flag = ", @maintain SELECT "!!! END Local Variables DEBUG Listing !!!" END /* verify file systems for replication on source server */ EXEC @CMDstatus = verify_sync_folders @dbName, @dumpPath, @dumpShare, @dumpFolder, @dumpArchive, @source, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* cleanup the source dump files */ EXEC @CMDstatus = delete_archive @dumpArchive, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* verify file systems for replication on target server */ EXEC @CMDstatus = verify_sync_folders @dbName, @loadPath, @loadShare, @loadFolder, @loadArchive, @target, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* cleanup the target dump files */ EXEC @CMDstatus = delete_archive @loadArchive, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* are source and target databases are the same size */ EXEC @CMDstatus = verify_sysusages @dbName, @source, @sourcePassword, @target, @targetPassword, @saPassword, @loadPath, @mailTo, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* count the number of rows in the candidate table in the source database for use later to verify the load */ SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -d' + @dbName + ' -Q"select count(*) as ' + @source + ' from ' + @candidateTable + '" -o' + @loadPath + '\sourceRows.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* set source db to be the primary server */ EXEC @CMDstatus = set_to_primary @dbName, @source, @sourcePassword, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* dump the database */ EXEC @CMDstatus = dumpDatabase @dbName, @source, @sourcePassword, @dumpFolder, @dumpDrive, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* Move the dump file to the target system */ EXEC @CMDstatus = zip_n_move @dbName, @dbName, -- as @fileName @source, @target, @sourcePassword, @targetPassword, @saPassword, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* configure the target database for load of source dump */ EXEC @CMDstatus = set_to_load @dbName, @target, @targetPassword, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* make sure no one else is in the target database */ EXEC @CMDstatus = admin.dbo.expunge_users @dbName, @target, @targetPassword, @saPassword, @loadPath, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* restore the dump to the target system */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"LOAD DATABASE ' + @dbName + " FROM DISK = '" + @loadDrive + ":\" + @loadFolder + "\" + @dbName + "\" + SUBSTRING(@dbName,1,4) +"dump.DAT" + "'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus EXEC verify_load @target, @loadDevice, @CMDstatus OUTPUT, @debug IF (@CMDstatus = 0) BEGIN SELECT @mailSubject = @dbName + ' dbSync Load failed.' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @message = @CMD RETURN 1 END /* configure the target database to be the hot site */ EXEC @CMDstatus = set_to_hot_site @dbName, @target, @targetPassword, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* perform data consistency check */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -d' + @dbName + ' -Q"select count(*) as ' + @target + ' from ' + @candidateTable + '" -o' + @loadPath + '\targetRows.txt' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* put an explaination header in the messenger file */ DELETE messenger where spid = @@SPID INSERT messenger SELECT @@SPID, 'Count of the rows in a candidate table on the' INSERT messenger SELECT @@SPID, 'source just before the dump and the same table' INSERT messenger SELECT @@SPID, 'on the target just after the load.' INSERT messenger SELECT @@SPID, '- CANDIDATE TABLE: ' + @candidateTable INSERT messenger SELECT @@SPID, '- DATABASE: ' + @dbName INSERT messenger SELECT @@SPID, '- SOURCE SYSTEM: ' + @source INSERT messenger SELECT @@SPID, '- TARGET SYSTEM: ' + @target INSERT messenger SELECT @@SPID, '***** target row count should be equal *****' INSERT messenger SELECT @@SPID, '.' EXEC hold_semaphore 'textreader' /* load the source system row count into messenger */ TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + '\sourceRows.txt -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus INSERT messenger SELECT @@SPID, text from textreader /* load the target system row count into messenger */ TRUNCATE TABLE textreader SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + '\targetRows.txt -c -Usa -P' + @saPassword + ' -S' + @@SERVERNAME EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus INSERT messenger SELECT @@SPID, text from textreader TRUNCATE TABLE textreader EXEC release_semaphore 'textreader' /* clean up consistency check files */ SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -dmaster -Q"master..xp_cmdshell ' + "'del " + @loadPath + "\*Rows.txt'" + '"' EXEC @CMDstatus = dispatch @CMD, @myName, @debug IF (@CMDstatus <> 0) RETURN @CMDstatus /* send the report to the mailto(s) if a problem exists */ SELECT @CMD = 'SELECT RTRIM(LTRIM(info)) FROM admin.dbo.messenger ' + 'WHERE info IS NOT NULL and spid = ' + CONVERT(VARCHAR(5), @@SPID) +' AND info NOT LIKE "(1 row %"' SELECT @mailSubject = @dbName + ' dbSync consistency check' IF (SELECT info FROM messenger WHERE ID = 13) <> (SELECT info FROM messenger WHERE ID = 16) BEGIN SELECT @mailSubject = @myName + ' ' + @dbName + ' CONSISTENCY ERROR' EXEC master.dbo.xp_sendmail @recipients = @mailto, @subject = @mailSubject, @query = @CMD END /* clean up admin environment */ DELETE messenger where spid = @@SPID TRUNCATE TABLE textreader /* maintain the database on the target system */ IF @maintain = 'true' EXEC maintenance @dbName, @target, 'false', -- do not dump this database @targetPassword, @saPassword, @debug END -- debug in batch RETURN GO