Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Execute these commands e.g. inside Microsoft SQL Server Management Studio Express to create the ExpressMaint stored procedure to back up your databases.
- Also see:
- - http://pastebin.com/znGVVkRj for the ExpressMaint prerequisites
- - http://expressmaint.codeplex.com for the ExpressMaint project
- */
- USE [master]
- GO
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [dbo].[expressmaint]
- (
- @database sysname, -- database name | ALL_USER | ALL_SYSTEM
- @optype varchar(7), -- LOG | DB | DIFF | REINDEX | REORG | CHECKDB
- @backupwith varchar(500) = NULL, -- additional backup options
- @backupfldr varchar(200) = NULL, -- folder to write backup to
- @reportfldr varchar(200) = NULL, -- folder to write text report
- @verify bit = 1, -- verify backup
- @verifywith varchar(500) = NULL, -- additional verify options
- @dbretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
- @dbretainval int = 1, -- specifies how many retainunits to keep backup
- @report bit = 1, -- flag to indicate whether to generate report
- @rptretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
- @rptretainval int = 1, -- specifies how many retainunits to keep reports
- @checkattrib bit = 0, -- check if archive bit is cleared before deleting
- @delfirst bit = 0, -- delete before backup (handy if space issues)
- @debug bit = 0, -- print commands to be executed
- @compression bit = 0, -- backup compression (SQL 2008 Enterprise Edition only)
- @continue_on_error bit = 1 -- whether to continue on error. UWE KEIM 2012-12-27.
- )
- AS
- /*
- ExpressMaintTSQL
- see http://www.sqldbatips.com/showarticle.asp?ID=27 for documentation
- Date Author Notes
- 24/07/2004 Jasper Smith Initial release
- 28/07/2008 Jasper Smith Fixed datepart issue with MONTHS retention unit
- 06/12/2008 Jasper Smith Added SQL 2008 support plus some minor fixes
- 12/27/2012 Uwe Keim Added option to continue on error
- */
- SET NOCOUNT ON
- SET ARITHABORT ON
- SET DATEFORMAT YMD
- /************************
- VARIABLE DECLARATION
- ************************/
- DECLARE @fso int
- DECLARE @file int
- DECLARE @reportfilename varchar(500)
- DECLARE @backupfilename varchar(500)
- DECLARE @delfilename varchar(500)
- DECLARE @cmd varchar(650)
- DECLARE @backupfldrorig varchar(200)
- DECLARE @databaseorig sysname
- DECLARE @table nvarchar(600)
- DECLARE @exists varchar(5)
- DECLARE @err int
- DECLARE @start datetime
- DECLARE @finish datetime
- DECLARE @runtime datetime
- DECLARE @output varchar(200)
- DECLARE @errormsg varchar(210)
- DECLARE @datepart nchar(2)
- DECLARE @execmd nvarchar(1000)
- DECLARE @delcmd nvarchar(1000)
- DECLARE @exemsg varchar(8000)
- DECLARE @filecount int ; SET @filecount = 0
- DECLARE @delcount int ; SET @delcount = 0
- DECLARE @hr int ; SET @hr = 0
- DECLARE @ret int ; SET @ret = 0
- DECLARE @cmdret int ; SET @cmdret = 0
- DECLARE @delbkflag int ; SET @delbkflag = 0
- DECLARE @delrptflag int ; SET @delrptflag = 0
- DECLARE @filecrt int ; SET @filecrt = 0
- DECLARE @user sysname ; SET @user = SUSER_SNAME()
- DECLARE @jobdt datetime ; SET @jobdt = GETDATE()
- DECLARE @jobstart char(12) ;
- DECLARE @stage int ; SET @stage = 1
- DECLARE @compressok bit ; SET @compressok = 0
- DECLARE @versionmajor int
- DECLARE @engineedition int
- SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),':',''),4)
- IF RIGHT(@reportfldr,1)<>'\' SET @reportfldr = @reportfldr + '\'
- IF RIGHT(@backupfldr,1)<>'\' SET @backupfldr = @backupfldr + '\'
- SET @backupfldrorig = @backupfldr
- SET @databaseorig = @database
- SELECT @versionmajor = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') as varchar(128)))-1) as int),
- @engineedition = CAST(SERVERPROPERTY('EngineEdition') as int)
- IF (@versionmajor>=10 AND @engineedition = 3) SET @compressok = 1
- CREATE TABLE #files(filename varchar(255))
- CREATE TABLE #exists(exist int,isdir int,parent int)
- CREATE TABLE #databases(dbname sysname)
- /**********************************
- INITIALIZE FSO IF @report = 1
- ***********************************/
- IF @report = 1
- BEGIN
- EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
- IF @hr <> 0
- BEGIN
- EXEC sp_OAGetErrorInfo @fso
- RAISERROR('Error creating File System Object',16,1)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- /************************
- CHECK INPUT
- ************************/
- -- check SQL2005 or higher
- IF @versionmajor<9
- BEGIN
- RAISERROR('SQL2005 or higher is required for sp_expressmaint',16,1)
- SET @ret = 1
- GOTO CLEANUP
- END
- -- check sysadmin
- IF IS_SRVROLEMEMBER('sysadmin') = 0
- BEGIN
- RAISERROR('The current user %s is not a member of the sysadmin role',16,1,@user)
- SET @ret = 1
- GOTO CLEANUP
- END
- -- check database exists and is online
- IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
- BEGIN
- IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
- BEGIN
- RAISERROR('Database %s is invalid or database status is not ONLINE',16,1,@database)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- -- check @optype is valid
- IF UPPER(@optype) NOT IN ('LOG','DB','DIFF','REINDEX','REORG','CHECKDB')
- BEGIN
- RAISERROR('%s is not a valid option for @optype',16,1,@optype)
- SET @ret = 1
- GOTO CLEANUP
- END
- -- check recovery mode is correct if trying log backup
- IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
- BEGIN
- IF (@optype = 'LOG' and ((select recovery_model from sys.databases where name = @database) = 3))
- BEGIN
- RAISERROR('%s is not a valid option for database %s because it is in SIMPLE recovery mode',16,1,@optype,@database)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- -- no log backups for system databases
- IF @database = 'ALL_SYSTEM'
- BEGIN
- IF @optype = 'LOG'
- BEGIN
- RAISERROR('%s is not a valid option for the option ALL_SYSTEM',16,1,@optype)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- -- check that @backupfldr exists on the server
- IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
- BEGIN
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@backupfldr
- IF @exists <> 'True'
- BEGIN
- RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- ELSE
- BEGIN
- INSERT #exists
- EXEC master.dbo.xp_fileexist @backupfldr
- IF (SELECT MAX(isdir) FROM #exists)<>1
- BEGIN
- RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- END
- -- check that @reportfldr exists on the server
- IF @reportfldr IS NOT NULL or @report = 1
- BEGIN
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@reportfldr
- IF @exists <> 'True'
- BEGIN
- RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- ELSE
- BEGIN
- DELETE #exists
- INSERT #exists
- EXEC master.dbo.xp_fileexist @reportfldr
- IF (SELECT MAX(isdir) FROM #exists)<>1
- BEGIN
- RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- END
- -- check @dbretainunit is a vaild value
- IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
- BEGIN
- IF UPPER(@dbretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES')
- BEGIN
- RAISERROR('%s is not a valid value for @dbretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@dbretainunit)
- SET @ret = 1
- GOTO CLEANUP
- END
- END
- --check @dbretainval is a vaild value
- IF @dbretainval<1
- BEGIN
- RAISERROR('%i is not a valid value for @dbretainval (must be >0)',16,1,@dbretainval)
- SET @ret = 1
- GOTO CLEANUP
- END
- -- check @rptretainunit is a vaild value if present
- IF UPPER(@rptretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES') and @rptretainunit IS NOT NULL
- BEGIN
- RAISERROR('%s is not a valid value for @rptretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@rptretainunit)
- SET @ret = 1
- GOTO CLEANUP
- END
- --check @rptretainval is a vaild value
- IF @rptretainval<1
- BEGIN
- RAISERROR('%i is not a valid value for @rptretainval (must be >0)',16,1,@rptretainval)
- SET @ret = 1
- GOTO CLEANUP
- END
- /***********************************
- list of databases to process
- ************************************/
- IF @database IN ('ALL_USER','ALL_SYSTEM')
- BEGIN
- IF @database = 'ALL_USER'
- INSERT #databases(dbname)
- SELECT [name] from sys.databases where database_id > 4
- AND (@optype <> 'LOG' OR recovery_model <> '3')
- ELSE
- INSERT #databases(dbname)
- SELECT [name] from sys.databases where database_id in (1,3,4)
- END
- ELSE
- INSERT #databases(dbname) SELECT @database
- /***********************************
- INITIALIZE REPORT IF @report = 1
- ************************************/
- -- generate report filename
- SELECT @reportfilename = @reportfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_'
- WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
- WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
- WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
- END + @jobstart + '.txt'
- -- if no report just set @reportfilename to NULL
- IF @report = 0 SET @reportfilename = NULL
- IF @debug = 1
- BEGIN
- PRINT '@reportfilename = ' + ISNULL(@reportfilename,'NULL')
- END
- IF @report = 1
- BEGIN
- -- create report file
- EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @reportfilename
- IF (@hr <> 0)
- BEGIN
- EXEC sp_OAGetErrorInfo @fso
- RAISERROR('Error creating log file',16,1)
- SET @ret = 1
- GOTO CLEANUP
- END
- ELSE
- -- set global flag to indicate we have created a report file
- SET @filecrt = 1
- -- write header
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- SET @output = 'Expressmaint utility, Logged on to SQL Server [' + @@SERVERNAME + '] as ' + '[' + @user + ']'
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- IF UPPER(@optype) NOT IN ('REINDEX','CHECKDB','REORG')
- BEGIN
- SET @output = 'Starting backup on ' + convert(varchar(25),getdate(),100)
- END
- IF UPPER(@optype) = 'CHECKDB'
- BEGIN
- SET @output = 'Starting CheckDB on ' + convert(varchar(25),getdate(),100)
- END
- IF UPPER(@optype) IN ('REINDEX','REORG')
- BEGIN
- SET @output = 'Starting Reindex on ' + convert(varchar(25),getdate(),100)
- END
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- /************************
- BACKUP ACTIONS
- ************************/
- IF UPPER(@optype) = 'CHECKDB' GOTO CHECK_DB
- IF UPPER(@optype) IN ('REINDEX','REORG') GOTO REINDEX
- -- if @delfirst = 1 we need to delete prior backups that qualify
- IF @delfirst = 1 GOTO DELFIRST
- -- this label is so that we can return here after deleting files if @delfirst = 1
- DOBACKUP:
- DECLARE dcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT dbname FROM #databases ORDER BY dbname
- OPEN dcur
- FETCH NEXT FROM dcur into @database
- WHILE @@FETCH_STATUS=0
- BEGIN
- -- set backup start time
- SET @start = GETDATE()
- -- write to text report
- IF @report = 1
- BEGIN
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': ' +
- CASE WHEN UPPER(@optype) = 'DB' THEN 'Full Backup '
- WHEN UPPER(@optype) = 'DIFF' THEN 'Differential Backup '
- WHEN UPPER(@optype) = 'LOG' THEN 'Log Backup '
- END + 'starting at ' + CONVERT(varchar(25),@start,100)
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- -- backup subfolder
- SET @execmd = 'IF NOT EXIST "' + @backupfldrorig + REPLACE(@database,'''','') + '\" MKDIR "' + @backupfldrorig + REPLACE(@database,'''','') + '\"'
- EXEC master.dbo.xp_cmdshell @execmd,no_output
- SET @backupfldr = @backupfldrorig + REPLACE(@database,'''','') + '\'
- SELECT @backupfilename = @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
- END + @jobstart +
- CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
- /************************
- FULL BACKUP
- ************************/
- IF UPPER(@optype) = 'DB'
- BEGIN
- IF @compression=1 AND @compressok=1
- SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
- ' WITH COMPRESSION' + CASE WHEN @backupwith IS NULL THEN '' ELSE (',' + @backupwith) END
- ELSE
- SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
- CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
- IF @debug = 1 PRINT 'FULL BACKUP : ' + @execmd
- BEGIN TRY
- EXEC(@execmd)
- END TRY
- BEGIN CATCH -- backup failure
- SELECT @err = @@ERROR,@ret = @err
- SELECT @errormsg = 'Full backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
- SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- IF @continue_on_error <> 1
- BEGIN
- CLOSE dcur
- DEALLOCATE dcur
- GOTO CLEANUP
- END
- END CATCH
- -- backup success
- SET @finish = GETDATE()
- SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- --calculate backup runtime
- SET @runtime = (@finish - @start)
- SET @output = SPACE(4) + 'Full database backup completed in '
- + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
- + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
- + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- END
- /************************
- DIFFERENTIAL BACKUP
- ************************/
- IF UPPER(@optype) = 'DIFF'
- BEGIN
- IF @compression=1 AND @compressok=1
- SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
- N' WITH DIFFERENTIAL,COMPRESSION' + CASE WHEN @backupwith IS NULL THEN N'' ELSE (N',' + @backupwith) END
- ELSE
- SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + N''' WITH DIFFERENTIAL' +
- CASE WHEN @backupwith IS NULL THEN N'' ELSE (N',' + @backupwith) END
- IF @debug = 1 PRINT 'DIFFERENTIAL BACKUP : ' + @execmd
- BEGIN TRY
- EXEC(@execmd)
- END TRY
- BEGIN CATCH -- backup failure
- SELECT @err = @@ERROR,@ret = @err
- SELECT @errormsg = 'Differential backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
- SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- IF @continue_on_error <> 1
- BEGIN
- CLOSE dcur
- DEALLOCATE dcur
- GOTO CLEANUP
- END
- END CATCH
- -- backup success
- SET @finish = GETDATE()
- SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- --calculate backup runtime
- SET @runtime = (@finish - @start)
- SET @output = SPACE(4) + 'Differential database backup completed in '
- + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
- + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
- + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- END
- /************************
- LOG BACKUP
- ************************/
- IF UPPER(@optype) = 'LOG'
- BEGIN
- IF @compression=1 AND @compressok=1
- SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
- ' WITH COMPRESSION' + CASE WHEN @backupwith IS NULL THEN '' ELSE (',' + @backupwith) END
- ELSE
- SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
- CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
- IF @debug = 1 PRINT 'LOG BACKUP : ' + @execmd
- BEGIN TRY
- EXEC(@execmd)
- END TRY
- BEGIN CATCH -- backup failure
- SELECT @err = @@ERROR,@ret = @err
- SELECT @errormsg = 'Log backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
- SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- IF @continue_on_error <> 1
- BEGIN
- CLOSE dcur
- DEALLOCATE dcur
- GOTO CLEANUP
- END
- END CATCH
- -- backup success
- SET @finish = GETDATE()
- SET @output = SPACE(4) + 'Log backed up to ' + @backupfilename
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- --calculate backup runtime
- SET @runtime = (@finish - @start)
- SET @output = SPACE(4) + 'Log backup completed in '
- + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
- + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
- + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- END
- SET @stage = (@stage + 1)
- FETCH NEXT FROM dcur into @database
- END
- CLOSE dcur
- DEALLOCATE dcur
- /************************
- VERIFY BACKUP
- ************************/
- IF @verify = 1
- BEGIN
- DECLARE dcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT dbname FROM #databases ORDER BY dbname
- OPEN dcur
- FETCH NEXT FROM dcur into @database
- WHILE @@FETCH_STATUS=0
- BEGIN
- SELECT @backupfilename = @backupfldrorig + REPLACE(@database,'''','') + '\' + REPLACE(REPLACE(@database,' ','_'),'''','_') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
- END + @jobstart +
- CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
- SET @start = GETDATE()
- -- write to text report
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Verify Backup File...'
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- SET @execmd = N'RESTORE VERIFYONLY FROM DISK = ''' + @backupfilename + '''' +
- CASE WHEN @verifywith IS NULL THEN '' ELSE (' WITH ' + @verifywith) END
- BEGIN TRY
- EXEC(@execmd)
- END TRY
- BEGIN CATCH
- SELECT @err = @@ERROR,@ret = @err
- SET @errormsg = 'Verify of ' + @backupfilename + ' failed with Native Error : ' + CAST(@err as varchar(10))
- SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- IF @continue_on_error <> 1
- BEGIN
- CLOSE dcur
- DEALLOCATE dcur
- GOTO CLEANUP
- END
- END CATCH
- -- verify success
- SET @finish = GETDATE()
- SET @output = SPACE(4) + 'Backup file ' + @backupfilename + ' verified'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- --calculate verify runtime
- SET @runtime = (@finish - @start)
- SET @output = SPACE(4) + 'Verify backup completed in '
- + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
- + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
- + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- SET @stage = (@stage + 1)
- FETCH NEXT FROM dcur into @database
- END
- CLOSE dcur
- DEALLOCATE dcur
- END
- /************************
- DELETE OLD FILES
- ************************/
- -- we have already deleted files so skip to the end
- IF @delfirst = 1 GOTO CLEANUP
- -- this label is so that we can delete files prior to backup if @delfirst = 1
- DELFIRST:
- /************************
- DELETE OLD BACKUPS
- ************************/
- SET @datepart = CASE
- WHEN UPPER(@dbretainunit) = 'MINUTES' THEN N'mi'
- WHEN UPPER(@dbretainunit) = 'HOURS' THEN N'hh'
- WHEN UPPER(@dbretainunit) = 'DAYS' THEN N'dd'
- WHEN UPPER(@dbretainunit) = 'WEEKS' THEN N'ww'
- WHEN UPPER(@dbretainunit) = 'MONTHS' THEN N'mm'
- END
- IF @debug = 1 PRINT '@datepart for backups = ' + @datepart
- -- write to text report
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files...'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- DECLARE dcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT dbname FROM #databases ORDER BY dbname
- OPEN dcur
- FETCH NEXT FROM dcur into @database
- WHILE @@FETCH_STATUS=0
- BEGIN
- SET @backupfldr = + @backupfldrorig + REPLACE(@database,'''','') + '\'
- SELECT @backupfilename = @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
- END + @jobstart +
- CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
- -- load files in @backupfldr
- IF @checkattrib = 1
- SET @cmd = 'dir /B /A-D-A /OD "' + @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
- CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
- ELSE
- SET @cmd = 'dir /B /A-D /OD "' + @backupfldr + REPLACE(REPLACE(@database,' ','_'),'''','_') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
- CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
- IF @debug = 1 PRINT '@cmd = ' + @cmd
- DELETE #files
- INSERT #files EXEC master.dbo.xp_cmdshell @cmd
- DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,''),'nothing')
- IF @debug = 1 SELECT * FROM #files
- -- get count of files that match pattern
- SELECT @filecount = COUNT(*) from #files
- WHERE PATINDEX('%File Not Found%',filename) = 0
- AND PATINDEX('%The system cannot find%',filename) = 0
- -- remove files that don't meet retention criteria if there are any files that match pattern
- IF UPPER(@dbretainunit) <> 'COPIES'
- BEGIN
- IF @filecount>0
- BEGIN
- SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +
- 'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''
- IF @debug = 1 PRINT '@delcmd=' + @delcmd
- EXEC master.dbo.sp_executesql @delcmd
- SELECT @delcount = COUNT(*) from #files
- END
- ELSE
- BEGIN
- SELECT @delcount = 0
- END
- END
- ELSE -- number of copies not date based (include current backup that's not in #files)
- BEGIN
- IF @filecount>0
- BEGIN
- IF @dbretainval>1
- BEGIN
- SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST((@dbretainval-1) as nvarchar(10)) +
- N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'
- IF @debug = 1 PRINT '@delcmd=' + @delcmd
- EXEC master.dbo.sp_executesql @delcmd
- END
- SELECT @delcount = COUNT(*) from #files
- END
- ELSE
- BEGIN
- SELECT @delcount = 0
- END
- END
- IF @debug = 1 PRINT '@delcount = ' + STR(@delcount)
- -- if there are any matching files
- IF @filecount>0
- BEGIN
- -- are there any files that need deleting
- IF @delcount>0
- BEGIN
- DECLARE FCUR CURSOR FORWARD_ONLY FOR
- SELECT * FROM #files
- OPEN FCUR
- FETCH NEXT FROM FCUR INTO @delfilename
- WHILE @@FETCH_STATUS=0
- BEGIN
- SET @cmd = 'DEL /Q "' + @backupfldr + @delfilename + '"'
- EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
- -- log failure to delete but don't abort procedure
- IF @cmdret<>0
- BEGIN
- SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @backupfldr + @delfilename + ' ***'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
- END
- ELSE
- BEGIN
- SET @output = SPACE(4) + 'Deleted file ' + @backupfldr + @delfilename
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- END
- FETCH NEXT FROM FCUR INTO @delfilename
- END
- CLOSE FCUR
- DEALLOCATE FCUR
- END
- END
- -- write to text report
- SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- FETCH NEXT FROM dcur into @database
- END
- CLOSE dcur
- DEALLOCATE dcur
- -- clear temporary table and variables
- DELETE #files
- SET @cmd = ''
- SET @delcmd = ''
- SET @delfilename = ''
- SET @datepart = ''
- SET @filecount = 0
- SET @delcount = 0
- SET @cmdret = 0
- SET @stage = @stage + 1
- /************************
- DELETE OLD REPORTS
- ************************/
- DELREPORTS:
- IF @rptretainunit IS NOT NULL
- BEGIN
- SET @datepart = CASE
- WHEN UPPER(@rptretainunit) = 'MINUTES' THEN N'mi'
- WHEN UPPER(@rptretainunit) = 'HOURS' THEN N'hh'
- WHEN UPPER(@rptretainunit) = 'DAYS' THEN N'dd'
- WHEN UPPER(@rptretainunit) = 'WEEKS' THEN N'ww'
- WHEN UPPER(@rptretainunit) = 'MONTHS' THEN N'mm'
- END
- IF @debug = 1 PRINT '@datepart for reports = ' + @datepart
- -- write to text report
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files...'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- -- load files in @reportfldr
- SET @cmd = 'dir /B /A-D /OD "' + @reportfldr + REPLACE(REPLACE(@databaseorig,' ','_'),'''','') +
- CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
- WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
- WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
- WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
- WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
- WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_' END + '*.txt"'
- IF @debug = 1 PRINT '@cmd = ' + @cmd
- INSERT #files EXEC master.dbo.xp_cmdshell @cmd
- DELETE #files WHERE filename IS NULL
- IF @debug = 1 SELECT * FROM #files
- -- get count of files that match pattern
- SELECT @filecount = COUNT(*) from #files
- WHERE PATINDEX('%File Not Found%',filename) = 0
- AND PATINDEX('%The system cannot find%',filename) = 0
- -- remove files that don't meet retention criteria if there are any files that match pattern
- IF UPPER(@rptretainunit) <> 'COPIES'
- BEGIN
- IF @filecount>0
- BEGIN
- SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@rptretainval as nvarchar(10)) + N',' +
- 'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
- + SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''
- IF @debug = 1 PRINT '@delcmd=' + @delcmd
- EXEC master.dbo.sp_executesql @delcmd
- SELECT @delcount = COUNT(*) from #files
- END
- ELSE
- BEGIN
- SELECT @delcount = 0
- END
- END
- ELSE -- number of copies not date based
- BEGIN
- IF @filecount>0
- BEGIN
- SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST(@rptretainval as nvarchar(10)) +
- N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'
- IF @debug = 1 PRINT '@delcmd=' + @delcmd
- EXEC master.dbo.sp_executesql @delcmd
- SELECT @delcount = COUNT(*) from #files
- END
- ELSE
- BEGIN
- SELECT @delcount = 0
- END
- END
- IF @debug = 1 PRINT STR(@delcount)
- -- if there are any matching files
- IF @filecount>0
- BEGIN
- -- are there any files that need deleting
- IF @delcount>0
- BEGIN
- DECLARE FCUR CURSOR FORWARD_ONLY FOR
- SELECT * FROM #files
- OPEN FCUR
- FETCH NEXT FROM FCUR INTO @delfilename
- WHILE @@FETCH_STATUS=0
- BEGIN
- SET @cmd = 'DEL /Q "' + @reportfldr + @delfilename + '"'
- EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
- -- log failure to delete but don't abort procedure
- IF @cmdret<>0
- BEGIN
- SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @reportfldr + @delfilename + ' ***'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
- END
- BEGIN
- SET @output = SPACE(4) + 'Deleted file ' + @reportfldr + @delfilename
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- END
- FETCH NEXT FROM FCUR INTO @delfilename
- END
- CLOSE FCUR
- DEALLOCATE FCUR
- END
- END
- -- write to text report
- SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- -- update stage
- SET @stage = @stage + 1
- END
- -- if we got here due to @delfirst = 1 go back and do the backups
- IF @delfirst = 1
- GOTO DOBACKUP
- ELSE
- GOTO CLEANUP
- /************************
- CHECKDB
- ************************/
- CHECK_DB:
- IF @optype = 'CHECKDB'
- BEGIN
- DECLARE dcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT dbname FROM #databases ORDER BY dbname
- OPEN dcur
- FETCH NEXT FROM dcur into @database
- WHILE @@FETCH_STATUS=0
- BEGIN
- -- write to text report
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Check Data and Index Linkage...'
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- -- set backup start time
- SET @start = GETDATE()
- SET @execmd = N'DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS'
- IF @debug = 1 PRINT 'DBCC Command : ' + @execmd
- BEGIN TRY
- EXEC(@execmd)
- END TRY
- BEGIN CATCH
- SELECT @err = @@ERROR,@ret = @err
- SET @errormsg = 'CheckDB of ' + @database + ' failed with Native Error : ' + CAST(@err as varchar(10))
- SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
- PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- IF @continue_on_error <> 1
- BEGIN
- CLOSE dcur
- DEALLOCATE dcur
- GOTO CLEANUP
- END
- END CATCH
- SET @finish = GETDATE()
- --calculate checkdb runtime
- SET @runtime = (@finish - @start)
- SET @output = SPACE(4) + 'CheckDB completed in '
- + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
- + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
- + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- SET @stage = (@stage + 1)
- FETCH NEXT FROM dcur into @database
- END
- CLOSE dcur
- DEALLOCATE dcur
- -- delete reports
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- GOTO DELREPORTS
- END
- /************************
- REINDEX/REORG
- ************************/
- REINDEX:
- IF @optype in ('REINDEX','REORG')
- BEGIN
- DECLARE dcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT dbname FROM #databases ORDER BY dbname
- OPEN dcur
- FETCH NEXT FROM dcur into @database
- WHILE @@FETCH_STATUS=0
- BEGIN
- -- write to text report
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- IF @optype = 'REINDEX'
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Rebuild (using original fillfactor)...'
- ELSE
- SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Reorganize...'
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- -- set start time
- SET @start = GETDATE()
- -- all user tables
- CREATE TABLE #tables(tablename sysname)
- EXEC(N'INSERT #tables(tablename) SELECT DISTINCT(''['' + s.[name] + ''].['' + t.[name] + '']'') FROM [' + @database + N'].sys.tables t ' +
- N'JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ' +
- N'JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ' +
- N'WHERE t.is_ms_shipped = 0 AND i.type>0')
- DECLARE tcur CURSOR LOCAL FAST_FORWARD
- FOR SELECT tablename FROM #tables ORDER BY tablename
- OPEN tcur
- FETCH NEXT FROM tcur INTO @table
- WHILE @@FETCH_STATUS = 0
- BEGIN
- IF @report = 1
- BEGIN
- IF @optype = 'REINDEX'
- SET @output = SPACE(4) + N'Rebuilding indexes for table ' + @table
- ELSE
- SET @output = SPACE(4) + N'Reorganizing indexes for table ' + @table
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- IF @optype = 'REINDEX'
- SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REBUILD'
- ELSE
- SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REORGANIZE'
- IF @debug = 1 PRINT 'Reindex Command : ' + @execmd
- BEGIN TRY
- EXEC(@execmd)
- END TRY
- BEGIN CATCH
- SELECT @err = @@ERROR,@ret = @err
- SET @errormsg = 'Rebuild of indexes on [' + @database + N'].' + @table + ' failed with Native Error : ' + CAST(@err as varchar(10))
- SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
- PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- END
- IF @continue_on_error <> 1
- BEGIN
- CLOSE tcur
- DEALLOCATE tcur
- DROP TABLE #tables
- GOTO CLEANUP
- END
- END CATCH
- FETCH NEXT FROM tcur INTO @table
- END
- CLOSE tcur
- DEALLOCATE tcur
- SET @finish = GETDATE()
- --calculate runtime
- SET @runtime = (@finish - @start)
- SET @output = SPACE(4) + 'Index maintenance completed in '
- + CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
- + CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
- + CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
- IF @debug = 1 PRINT @output
- IF @report = 1
- BEGIN
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- END
- DROP TABLE #tables
- SET @stage = (@stage + 1)
- FETCH NEXT FROM dcur into @database
- END
- CLOSE dcur
- DEALLOCATE dcur
- -- delete reports
- GOTO DELREPORTS
- END
- /************************
- CLEAN UP
- ************************/
- CLEANUP:
- DROP TABLE #files
- DROP TABLE #exists
- DROP TABLE #databases
- -- if we encountered errors deleting old backups return failure
- IF @delbkflag<>0
- BEGIN
- SET @errormsg = 'Expressmaint encountered errors deleting old backup files' + CHAR(13)
- + CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
- RAISERROR(@errormsg,16,1)
- SET @ret = 1
- END
- -- if we encountered errors deleting old reports return failure
- IF (@delrptflag<>0 AND @delbkflag = 0)
- BEGIN
- SET @errormsg = 'Expressmaint encountered errors deleting old report files' + CHAR(13)
- + CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
- RAISERROR(@errormsg,16,1)
- SET @ret = 1
- END
- -- if we created a file make sure we write trailer and destroy object
- IF @filecrt = 1
- BEGIN
- -- write final part of report
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- SET @output = 'Expressmaint processing finished at ' + CONVERT(varchar(25),GETDATE(),100)
- + ' (Return Code : ' + CAST(@ret as varchar(10)) + ')'
- IF @debug = 1 PRINT @output
- EXEC sp_OAMethod @file,'WriteLine',NULL,@output
- EXEC sp_OAMethod @file,'WriteLine',NULL,''
- -- destroy file object
- EXEC @hr=sp_OADestroy @file
- IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
- END
- IF @report = 1
- BEGIN
- EXEC @hr=sp_OADestroy @fso
- IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
- END
- RETURN @ret
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement