IF EXISTS (SELECT *
FROM sysobjects
WHERE id = object_id('dbo.logSync')
AND sysstat & 0xf = 4)
DROP PROCEDURE dbo.logSync
GO
CREATE PROCEDURE logSync
@dbName VARCHAR(30) = ' ', -- the database name on both systems
@candidateTable VARCHAR(30) = ' ', -- for verification of restore (rowcount)
@source VARCHAR(30) = 'prod', -- the source server name
@sourcePassword VARCHAR(30) = ' ', -- sa password on source
@dumpShare VARCHAR(10) = 'alle', -- drive share name where dump folder resides
@dumpFolder VARCHAR(30) = 'backup', -- source sys top level (full path less drive)
@target VARCHAR(30) = 'hot_site', -- the target server name
@targetPassword VARCHAR(30) = ' ', -- sa password on target
@loadShare VARCHAR(10) = 'alle', -- drive share name where load folder resides
@loadFolder VARCHAR(30) = 'restore', -- target sys top level (full path less drive)
@mailto VARCHAR(30) = 'DBA', -- valid account in same Exchange Server
@saPassword VARCHAR(30) = ' ', -- sa password on admin server
@debug VARCHAR(5) = 'false'
AS
DECLARE
@dumpPath VARCHAR(50), -- fully qualified dump path
@dumpDevice VARCHAR(80), -- fully qualified dump device
@dumpArchive VARCHAR(80), -- source archive PATH
@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
@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
/*
Procedure logSync will dump a database and restore the dump to a 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 = 'admin.logSync'
/* verify some parms - exit if inconsistent */
IF (@dbName = ' ') OR (@candidateTable = ' ')
BEGIN
PRINT " usage: logSync [@dbname = <name of the database to be replicated > ],"
PRINT " [@candidateTable = <table used for inter-db consistency check> ],"
PRINT " [@source = <source SQL Server's name> ],"
PRINT " [@sourcePassword = <sa password on the source SQL Server> ],"
PRINT " [@dumpShare = <share name where dump folder resides> ],"
PRINT " [@dumpFolder = <source system root (full path less drive)> ],"
PRINT " [@target = <target SQL Server's name> ],"
PRINT " [@targetPassword = <sa password on target SQL Server> ],"
PRINT " [@loadShare = <drive share name where load folder resides>],"
PRINT " [@loadFolder = <target system root (full path less drive)> ],"
PRINT " [@mailto = <internet email address of administrator> ],"
PRINT " [@saPassword = <sa password on adminstration SQL Server> ],"
PRINT " [@maintFlag = <flag to include maintenance after restore> ],"
PRINT " [@debug = <debug mode flag - default is false> ],"
RETURN 1
END
/* make sure the db is available else exit*/
EXEC @CMDstatus = isAdminUsingDB @dbName, @source, @target, @debug
IF @CMDstatus <> 0
BEGIN
SELECT @CMD = 'An adminstrative subsystem process is already active in database '
+ @dbName + '. Only one admin process may be active at any time.('
+ @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
SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' +
@dumpFolder + '\' + @dbName
SELECT @dumpArchive = @dumpPath + '\archive'
SELECT @loadPath = '\\' + @target + '\' + @loadShare + '\' +
@loadFolder + '\' + @dbName
SELECT @loadArchive = @loadPath + '\archive'
IF (@debug = 'true')
BEGIN
SELECT '@myName: ',@myName
SELECT '@dumpPath: ',@dumpPath
SELECT '@dumpArchive: ',@dumpArchive
SELECT '@loadPath: ',@loadPath
SELECT '@loadArchive: ',@loadArchive
END
/* make sure no one else is in the target database else load will fail */
EXEC @CMDstatus = 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 = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
/* make sure a source system archive path exists for this database */
SELECT @CMD = 'dir ' + @dumpArchive
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
/* make sure a target system archive path exists for this database */
SELECT @CMD = 'dir ' + @loadArchive
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
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 '@myName: ',@myName
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
/* 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' + @dumpDevice + '.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 = @dumpDevice + '.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
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 */
SELECT @fileName = SUBSTRING(@fileName,1,CHARINDEX('.DAT',@fileName) - 1)
EXEC @CMDstatus = zip_n_move @dbName,
@filename,
@source,
@sourcePassword,
@dumpShare,
@dumpFolder,
@target,
@targetPassword,
@loadShare,
@loadFolder,
@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' + @loadDevice + '.out'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
EXEC verify_load @target,
@loadDevice,
@CMDstatus OUTPUT,
@debug
If (@CMDstatus = 0)
BEGIN
SELECT @CMD = @loadDevice + '.out'
SELECT @mailSubject = @dbName + ' logSync Load failed. dbSync has been initiated'
EXEC master.dbo.xp_sendmail @recipients = @mailto,
@subject = @mailSubject,
@attachments = @CMD
SELECT @task = 'dbSync ' + @dbName + ' ' + @source + ' to ' + @target
EXEC msdb..sp_runtask @task
RETURN 1
END
ELSE
BEGIN
/* configure the target database to be 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'
END
RETURN
GO