Auto BackUp system: Sometimes we need to implement auto backup system.
Solution: You need to complete the following tasks step by step
Step-1: Execute the following code
use master
GO
-- set required options
exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure 'xp_cmdshell',1
reconfigure
go
exec sp_configure 'Ole Automation Procedures',1
reconfigure
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[expressmaint]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[expressmaint]
GO
CREATE 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)
)
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
*/
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
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
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
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
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
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
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
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
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
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
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
CLOSE tcur
DEALLOCATE tcur
DROP TABLE #tables
GOTO CLEANUP
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
GO
PRINT 'Stored Procedure created successfully'
Step 2:
Create a schedule like jobcommand.sql ;
set necessary info
like location , backfile location, report file location
After that run the userfullbackup under master
exec expressmaint
@database = 'YourDatabaseName',
@optype = 'DB',
@backupfldr = 'D:\Automated Backup\backups',
@reportfldr = 'D:\Automated Backup\reports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 1,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1
Step 3: Create Folder in your computer according to above information.
Step 4: Set scheduler necessary information.
