Forgot to mention that I also have another trigger for when a database is removed:
CREATE TRIGGER [DDLTriggerRemoveDatabaseBackupSchedule]
ON ALL SERVER
FOR DROP_DATABASE
AS
declare @data xml
declare @DBName varchar(256)
set @data = eventdata()
set @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
delete master.dbo.backupschedule
where database_Name = @DBName
We have a strange setup here where we don't have any need for Point in Time disaster recovery so I have made sure that all the DBs are in SIMPLE recovery. The backup schedule is what was setup originally before I joined the company three months ago and I'm happy with it.
If there is a need to perform T-Log backups in future then obvioulsy I will have to modify my sproc to handle T-log backups too.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn