As I am beginning our journey into SQL 2008, I am taking this time to go through all our scripts and try to resolve some of the issues I get alerted on that are due to how we do things. One of the things we do is programatically list all the databases, determine which are prod, test, & system. Then the Prod databases have xlog backups and the others do not. Occasionally, some of our prod databases will change from Full Recovery Mode to Simple Recovery Mode. How I find out about this is that the Xlog backup job will fail. The first database that should have been in Full but was in Simple would fail the whole job and transaction logs would not be backed up. Failing jobs is a horrible way to find out that a recovery mode changed. So I re-wrote our backup job to query sys.databases and do xlog backups to any database in full recovery mode. No more xlog failures for wrong setting. Life is good. But wait, what if a database does change modes how will I know? Sure the xlogs of databases that are in full recovery mode backed up, but that may not be good enough. So I sat back down and started wrote an new script. This script looks at yesterday’s list of Simple Recovery Mode servers and compares it to Today’s list. If there is a new database in Simple recovery mode it sends me an e-mail to investigate it. If there is no change, it just ends. Here’s the code.
declare @NewSimple int, @subject varchar(256)
select name into maintenance..backupsimple from sys.databases where recovery_model = 3
SELECT name into ##backupchange
FROM
(
SELECT name
FROM maintenance..backupsimple
EXCEPT
SELECT name
FROM maintenance..backupsimpleold
) tmp
GROUP BY name
HAVING COUNT(*) = 1
ORDER BY name
select @NewSimple = COUNT(name) from ##backupchange
if @NewSimple >0
BEGIN
select @subject = ‘New Simple Mode Databases on server ‘ + replace(@@servername, ‘\’, ‘_’)
exec msdb..sp_send_dbmail
@profile_name = ‘SQL Server’,
@recipients = ‘<My E-mail address>’,
@importance = N’HIGH’,
@subject = @subject,
@body = ‘Please verify the following databases should be in Simple Recovery Mode. They were not in Simple Recovery mode yesterday. Remember to perform a full backup if you change the model to full recovery mode:’,
@query = ‘select name from ##backupchange’
END
drop table maintenance..backupsimpleold
select name into maintenance..backupsimpleold from maintenance..backupsimple
drop table maintenance..backupsimple
drop table ##backupchange
I have this running once a day for now. Maybe in the future it will need to run more often.
