IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID('dbo.maintenance')
AND sysstat & 0xf = 4)
DROP PROCEDURE dbo.maintenance
GO
CREATE PROCEDURE maintenance
@dbName VARCHAR(30) = 'bwtest', -- the database name on both systems
@source VARCHAR(30) = 'prod', -- the source server name
@sourcePassword VARCHAR(30) = ' ', -- sa password on source
@dumpShare VARCHAR(30) = 'alle', -- source system drive share name
@dumpFolder VARCHAR(30) = 'backup', -- source system top level directory
@dumpMe VARCHAR(5) = 'true', -- flag to enable dump processing
@mailto VARCHAR(30) = 'DBA', -- valid account in same Exchange Server
@saPassword VARCHAR(30) = ' ', -- sa password for admin server
@debug VARCHAR(5) = 'false' -- debug mode flag
AS
DECLARE
@dumpPath VARCHAR(50), -- dump path
@dumpDevice VARCHAR(80), -- fully qualified dump device
@dumpArchive VARCHAR(80), -- source archive PATH
@CMD VARCHAR(255), -- reusable command holder
@CMDstatus INT, -- return status of command prompt
@mailSubject VARCHAR(80), -- mail subject string
@mailMessage VARCHAR(80), -- mail message string
@myName VARCHAR(30), -- this stored procedure
@results VARCHAR(80), -- file to hold outfile scan info
@outfile VARCHAR(80), -- working var for qualified file name
@drive varchar(5) -- the logical name of a shared drive
/*
Procedure will perform maintenance and dump of a
database. The previous dump will be kept in a
zipped archive along with the current dump.
*/
SET NOCOUNT ON
SELECT @myName = "admin.maintenance"
/* make sure the db is available else exit*/
EXEC @CMDstatus = isAdminUsingDB @dbName, @source, ' ', @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
/* 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 @results = @dumpPath + '\ck_outfiles.results'
IF @debug <> 'true'
BEGIN
SELECT "@myName: ", @myName
SELECT "@dumpPath: ", @dumpPath
SELECT "@dumpDevice: ", @dumpDevice
SELECT "@dumpArchive: ", @dumpArchive
SELECT "@results: ", @results
END
/* 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 = dispatch @CMD, @myName, @debug
IF (@CMDstatus = 0) -- found
BEGIN
SELECT @CMD = 'dir ' + @dumpPath + '\*.001'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
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 locking as 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 to 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 to 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 over 90% 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
/* if this msdb is local clean up systasks */
IF @source = @@SERVERNAME
BEGIN
SELECT @CMD = "admin..purgeMSDB @source = '" + @source +
"', @sourcePassword = '" + @sourcePassword + "'"
EXEC (@CMD)
END
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,
@dumpDevice,
@debug
IF (@CMDstatus <> 0) RETURN @CMDstatus
/* archive the dump */
EXEC convert_share_to_drive @dumpShare,
@drive OUTPUT,
@dumpPath,
@source,
@sourcePassword,
@saPassword,
@debug
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
RETURN 0
GO