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