IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID('dbo.dbSync')
AND sysstat & 0xf = 4)
DROP PROCEDURE dbo.dbSync
GO
CREATE PROCEDURE dbSync
@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 system 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 system top level (full path less drive)
@mailto VARCHAR(30) = 'DBA', -- valid account in same Exchange Server
@saPassword VARCHAR(30) = ' ', -- sa password on admin server
@maintFlag VARCHAR(5) = 'true', -- do maintenance after the restore
@debug VARCHAR(5) = 'false' -- debug this procedure
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),
@myName VARCHAR(30)
/*
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
systems.
*/
SET NOCOUNT ON
SELECT @myName = 'admin.dbSync'
/* if no dbName display usage message and exit */
IF (@dbName = ' ') OR (@candidateTable = ' ')
BEGIN
PRINT " usage: dbSync [@dbname = <name of the database to be replicated> ],"
PRINT " [@candidateTable = <table for rowcount compare 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
/* 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 "@myName: ", @myName
SELECT "@dumpPath: ", @dumpPath
SELECT "@dumpDevice: ", @dumpDevice
SELECT "@dumpArchive: ", @dumpArchive
SELECT "@loadPath: ", @loadPath
SELECT "@loadDevice: ", @loadDevice
SELECT "@loadArchive: ", @loadArchive
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) select @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 @dumpArchive, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
/* verify that the 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*/
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,
@dumpDevice,
@debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
EXEC verify_dump @source,
@dumpDevice,
@CMDstatus OUTPUT,
@debug
If (@CMDstatus = 0)
BEGIN
SELECT @CMD = 'Dump of database ' + @dbName +
' not found in msdb..sysbackuphistory 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 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
/* Move the dump file to the target system */
EXEC @CMDstatus = zip_n_move @dbName,
@dbName, -- as @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
/* 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
/* restore the dump to the target system */
SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target +
' -dmaster -Q"LOAD DATABASE ' + @dbName +
" FROM DISK = '" + RTRIM(LTRIM(@loadDevice)) + "'" + '"'
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 source'
INSERT messenger
SELECT @@SPID, 'system just before the database is dumped and the same table'
INSERT messenger
SELECT @@SPID, 'on the target system just after the database 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, '***** 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 + "\sourceRows.txt'" + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target +
' -dmaster -Q"master..xp_cmdshell ' + "'del " +
@loadPath + "\targetRows.txt'" + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
/* send the report to the mailto(s) */
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 = @dbName + ' dbSync consistency check discrepency report'
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 @maintFlag = 'true'
EXEC maintenance @dbName,
@target,
@targetPassword,
@loadShare,
@loadFolder,
'false', -- do not dump this database
@mailto,
@saPassword,
@debug
RETURN
GO