Full and Differential Backup of Select Databases

CREATE procedure DP_DiffDatabaseBackup @@DatabaseType varchar(5)
as
begin
	
	-- Variable declaration for the backups
	
	declare @LogFileInfo varchar(255)
	declare @Date varchar(10)
	declare @DatabaseName varchar(255)
	declare @StatusReport varchar(1000)
	declare @DirName varchar(255)
	declare @ArcDirName varchar(255)
	declare @SQL varchar (255)
	declare @SQL2 varchar (255)
	declare @Counter tinyint
	set @DirName = 'D:\SQL2000\DiffBack\'
	set @ArcDirName = 'D:\SQL2000\DiffArch\'
	set @counter = 1
	set @LogFileInfo = ''
	set @Date = ''	
	set @StatusReport=''
	set @SQL2=''

	-- Checking for enough free space to perform the backups
	
	declare @FileText varchar(255)
	declare @FileTextLength tinyint
	declare @FileSize varchar(255) 
	declare @FileSizeInMB bigint
	
	set @FileTextLength = 1
	
	create table #DirName
	(
		context varchar(255)
	)
	
	set @SQL2 = 'dir D:\SQL2000\Data\' + @@DatabaseType + '*'
	insert into #DirName exec xp_cmdshell @SQL2
	
	select @FileText=Rtrim(Ltrim(context)) from #DirName where context like '%file(s)%'
	
	set @FileSize = replace(@FileText,' bytes','')
	set @FileSize = replace(@FileSize,'File(s)','')
	
	while @FileTextLength < len(@FileSize)
	begin
		if right(left(@FileSize,@FileTextLength),1) = ' '
			break
		set @FileTextLength = @FileTextLength + 1
	end
	
	set @FileSize = right(@FileSize,(len(@FileSize) - @FileTextLength))
	set @FileSize = replace(@FileSize,' ','')
	set @FileSize = replace(@FileSize,',','')
	set @FileSize = rtrim(ltrim(@FileSize))
	
	set @FileSizeInMB = cast(@FileSize as bigint)/1024/1024
	
	select @FileSizeInMB
	
	drop table #DirName
	
	create table #DriveSpace
	(
		DriveName char(1),
		DriveSpace int
	)
	
	insert into #DriveSpace exec xp_fixeddrives
	
	if (select DriveSpace from #DriveSpace where DriveName = 'D') < @FileSizeInMB
		set @StatusReport =  @StatusReport + ' Not enough disk space to perform backups... ' 
	else
	begin
	
		-- Backup Commencing
	
		-- Archiving differential database backups to the archive folder
		
		set @SQL = 'copy ' + @DirName + '*Diff.bak ' + @ArcDirName + '*' 
		exec xp_cmdshell @SQL
		
		if @@error <> 0 
		begin
			set @StatusReport = @StatusReport + ' Unable to archive the backup file... ' 
			return
		end
		
		set @SQL = 'del ' + @DirName + '*Diff.bak ' 
		exec xp_cmdshell @SQL
		
		if @@error <> 0 
		begin
			set @StatusReport = @StatusReport + ' Unable to delete all the files in the backup folder... ' 
			return
		end

	
		-- Creating a temp table to populate the log file information
				
		create table #LogFiles
		(
			LogFileInfo varchar(255)
		)
		
		-- Creating a temp table to populate the database information
		
		create table #PopulateDatabases
		(
			ID tinyint identity,
			DatabaseName varchar(255) not null
		)
		
		-- Inserting all active databases to the temp table
		
		insert into #PopulateDatabases 
		select name from master.dbo.sysdatabases d  
		where (d.status & 992 = 0)  
		and name like @@DatabaseType + '%'
		and ((DATABASEPROPERTY(d.name, 'issingleuser') = 0 and (has_dbaccess(d.name) = 1)) 
		or  ( DATABASEPROPERTY(d.name, 'issingleuser') = 1 
		and not exists  (select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))

		-- Getting the log file information

		insert into #LogFiles exec xp_cmdshell 'dir /O-D E:\SQL2000\log'
		delete from #LogFiles where LogFileInfo not like '%ldf%' or LogFileInfo is null
		
		-- Process of backing up starts here
		
		while @counter <= (select max([ID]) from #PopulateDatabases)
		begin
	
			select @DatabaseName=DatabaseName from #PopulateDatabases where [ID] = @counter
			select @LogFileInfo = LogFileInfo from #LogFiles where LogFileInfo like '%'+@DatabaseName+'%'

			if @@rowcount <> 0
			begin

				set @Date = Left(@LogFileInfo,10)
				if cast(@Date as datetime) > getdate()-2
				begin
			
					select distinct(logical_name) from msdb..backupfile
						where logical_name like @@DatabaseType + '%' and logical_name like '%'+@DatabaseName+'%'
				
					if @@rowcount = 0
					begin
		
						set @StatusReport = @StatusReport + ' The Database ' + @DatabaseName + ' has never been backed up before... Backing up for the first time...'
		
						set @SQL = 'backup database ' + @DatabaseName + ' to disk =' + ''''
						set @SQL = @SQL +  'D:\SQL2000\FullBack\' + @DatabaseName + '_'
						set @SQL = @SQL + (convert(varchar(8), getdate(),112) )
						set @SQL = @SQL + '_Full.bak' + ''''
					
						exec (@sql)
		
						set @StatusReport = @StatusReport + ' The Database ' + @DatabaseName + ' has been backed up successfully...'
		
					end
				
					set @SQL = 'backup database ' + @DatabaseName + ' to disk =' + ''''
					set @SQL = @SQL + @DirName + @DatabaseName + '_'
					set @SQL = @SQL + (convert(varchar(8), getdate(),112) )
					set @SQL = @SQL + '_Diff.bak' + ''''  + ' with DIFFERENTIAL '
				
					exec (@sql)
				
					set @StatusReport = @StatusReport + ' The Database ' + @DatabaseName + ' has been backed up successfully...'
				
				end

			end

			set @counter = @counter + 1
			
		end
		
		-- Dropping the temp tables
		
		drop table #PopulateDatabases
		drop table #LogFiles
	
	end
	
	drop table #DriveSpace
	

	-- Displaying error messages
	
	declare @MailSubject varchar(255)
	set @MailSubject = (select @@servername)
	set @MailSubject = 'SQL Server Differential Backup Report of ' + @@DatabaseType + ' Databases on Server : ' + @MailSubject
	EXEC xp_sendmail @recipients = 'databaseadministrators@abcd.com', 
		   @message = @StatusReport,
		   @subject = @MailSubject

end
GO