So, Like a good boy, I have my SQL Server databases in full recovery model. Because of this, I also employ an aggressive backup strategy. The databases get a full backup weekly to tape, differential backup daily to tape, and transaction log backup to disk every 15 minutes.
We have a few robust SQL Server boxes each of which house lots of databases. I want the same backup strategy applied to each one. For the tape backups, we're employing HP OpenView Storage DataProtector which has a nice interface for doing just such a task.
There isn't a nice little tool (that I've found) in SQL Server to do this however. So, I crafted my own.
I originally had the transaction log files all going to the same file for a particular database. Since transaction logs are based on the last full or differential backup, this made the restores really complicated. I wanted to make sure that there was only 1 transaction log backup file per differential or full backup. So I baked that into the script as well.
Now, since I'm backing up the transaction logs to disk and I want those stored onto tape, there is a job in our tape backup that will also backup all the Transaction Log Backup Files. Finally, I wanted to delete the transaction log backup file has been archived to tape.
Hopefully all this makes sense. And now... the code:
/*
Transaction Log Maintenance Script.sql
Rob Bittner
August 5, 2009
Script to back up all the transaction logs on a database and warn if there
are file backups instead of tape backups
1) For Each Database:
- If the last backup was not a tape backup email the help desk.
- Otherwise, back up the transaction log.
2) Go through all the existing Transaction Log Backup files and delete those
that have been backed up to tape through the nightly file backup.
Modifications:
November 18, 2009 (RB) : Changed Email to go to the help desk instead of me
*/
declare cur cursor for
select name
from sys.databases
where state_desc='online' and recovery_model_desc='full' and name <> 'model'
open cur
declare @backuppath varchar(255)
declare @name varchar(255)
declare @filename varchar(255)
declare @lastbackupdate datetime
set @backuppath='e:\sqldata\Backup\'
fetch next from cur into @name
while @@fetch_status = 0 begin
print 'Backing up ' + @name
select @lastbackupdate=max(backup_start_date) from msdb.dbo.backupset where type in ('i','d') and database_name=@name
set @filename= @backuppath + @name + ' Transaction Log Backup ' + replace(convert(varchar,@lastbackupdate,120),':','') + '.bak'
if not exists ( -- see if there was no tape backup the last time is was backed up
select 1
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
and device_type = 7) begin
if not exists (
select 1 -- See if there already was a backup attempt (to prevent multiple emails)
from msdb..sysjobhistory
where job_id = (select job_id from msdb..sysjobs where name='Transaction Log Maintenance')
and step_id=0 -- job finished
and CONVERT -- date started
( DATETIME, RTRIM(run_date) ) +
( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 ) / 216e4
> @lastbackupdate
) begin
declare @body varchar(max)
declare @path varchar(max)
declare @subject varchar(255)
select @path=bmf.physical_device_name
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
order by backup_start_date
set @subject='Transaction log backup failed on ' + @@servername
set @body=
'<html><head><style type="text/css">body{font-family: calibri;} h1,h2,h3 { margin:0}</style></head>'+
'<body>'+
'<h1>Error when backing up transaction log</h1>' +
'<h2>Most recent backup on the database is a file backup</h2><hr />' +
'<h3>Server: ' + @@servername + '</h3>' +
'<h3>Last Backup Path: ' + @path + '</h3>' +
'<h3>Last Backup Time: ' + convert(varchar,@lastbackupdate,109) + '</h3>' +
'<p style="font-weight:bold">To preserve the integrity of our database backup process, a full backup of ' + @name + ' must be performed utilizing Dataprotector ASAP!</p>'+
'<h2>Failure to back up this database to tape will cause the transaction log to continue to grow</h2>' +
'<p> - SQL Server Database Mailer</p>' +
'</body>' +
'</html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients='email address here',
@subject = @subject,
@body = @body,
@body_format = 'HTML',
@profile_name='HelpDesk' ;
end -- Only email once if statement
end else begin
backup log @name to disk=@filename
end
fetch next from cur into @name
end
close cur
deallocate cur
declare @deletestatement varchar(255)
set @deletestatement = 'del ' + @backuppath + '* /a-a /q'
print @deletestatement
EXEC master..xp_cmdshell @deletestatement
