Agent Jobs

Use DDL Triggers Create Backup Agent Jobs Automatically

here’s some DDL triggers that i’ve used to automatically create backup jobs for each new database that is added
and will automatically delete the backup job when a database is dropped.

DDL_Trig_AutoCreate_BUJob
DDL_Trig_AutoDelete_BUJob

keep in mind this script is taken from some old notes which means there is certainly room for improvement.
every time i used these i tend to modify it here and there for other purposes.

for example; suppose someone renames a database… well you might want to incorporate a trigger for
‘ALTER DATABASE’ events so the new name can be captured and applied to it’s corresponding job, or you can
avoid this completely by modifying the trigger to use the database id, and not the database name. it’s up to you.

you may find some technical challenges when using these triggers. for example they may not permit you to
drop a backup job without first dropping the database. no bigy; just disable or drop the triggers all together,
and proceed with whatever you were going to do, or better yet… mod them, and post back in this thread

here’s a quick drop statement:

/************************************/
drop trigger DDL_Trig_AutoDeleteBUJob
on all server
go
/************************************/

at the very least these triggers can get you started in creating your own cool DDL automations.  if so... please post
the cool backup/maintenance stuff you've done in this thread to contribute back to the community 

ok...  on with the scripts.   here you go.



/**************************************************/ /**************************************************/ 
-- This will create a standard SQL Backup Job for all Databases. /**************************************************/ 

declare @mydb varchar(60) 
declare GetDBname cursor read_only for select name from master..sysdatabases 
open GetDBname fetch next from GetDBname into @mydb 

declare @desc varchar(50) while @@fetch_status = 0 
begin set @desc = 'Full Database Backup ' + @MYDB exec msdb.dbo.sp_add_job @job_name= @desc
, @enabled=1
, @notify_level_eventlog=0
, @notify_level_email=0
, @notify_level_netsend=0
, @notify_level_page=0
, @delete_level=0
, @description=@desc
, @category_name='[uncategorized (local)]'
, @owner_login_name='sa' fetch next from GetDBname into @mydb end close GetDBname deallocate GetDBname 
go 

/**************************************************/ 
declare @mydb varchar(60) declare GetDBname cursor read_only for select name from master..sysdatabases 
open GetDBname fetch next from GetDBname into @mydb
 
declare @desc varchar(50) 
declare @command varchar (200) 
--declare @outputfile varchar (50) 
while @@fetch_status = 0 begin set @desc = 'Full Database Backup ' + @MYDB 
set @command = 'BACKUP DATABASE [' + @mydb + '] TO DISK = N''E:\MSSQL\Backup\' + @mydb + '.bak'' WITH INIT , NOUNLOAD , NAME = N''' + @mydb + ' Backup'', NOSKIP , STATS = 10, NOFORMAT' 
--set @outputfile = 'D:\BackupReport\' + @mydb + '.txt' EXEC msdb.dbo.sp_add_jobstep @job_name = @desc
, @step_name=N'Full Backup'
, @step_id=1
, @cmdexec_success_code=0
, @on_success_action=1
, @on_success_step_id=0
, @on_fail_action=2
, @on_fail_step_id=0
, @retry_attempts=0
, @retry_interval=0
, @os_run_priority=0
, @subsystem=N'TSQL'
, @command=@command
, @database_name=@mydb
, --@output_file_name=@outputfile
, @flags=2 fetch next from GetDBname into @mydb end close GetDBname deallocate GetDBname 
go 

/**************************************************/ 
declare @mydb varchar (60) 
declare GetDBname cursor read_only for select name from master..sysdatabases 
open GetDBname fetch next from GetDBname into @mydb 

declare @desc varchar(50) while @@fetch_status = 0 begin set @desc = 'Full Database Backup ' + @mydb EXEC msdb.dbo.sp_add_jobschedule @job_name = @desc
, @name=N'Backup'
, @enabled=1
, @freq_type=4
, @freq_interval=1
, @freq_subday_type=1
, @freq_subday_interval=0
, @freq_relative_interval=0
, @freq_recurrence_factor=0
, @active_start_date=20050419
, @active_end_date=99991231
, @active_start_time=0
, @active_end_time=235959 EXEC msdb.dbo.sp_add_jobserver @job_name = @desc
, @server_name = N'(local)' fetch next from GetDBname into @mydb end close GetDBname deallocate GetDBname 
go 

/****************************************************************************/ /****************************************************************************/ 
-- this trigger will create a new sql database backup job for each new -- database that is added. /****************************************************************************/
 --drop trigger DDL_Trig_AutoCreateBUJob 
--on all server 
--go 

Create trigger DDL_Trig_AutoCreateBUJob on all server for create_database as 
-- print 'create database issued.' 
set nocount on 
declare @mydb varchar(150) declare @desc varchar (150) set @mydb = CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVarchar(128)) 
set @desc = 'Full Database Backup ' + @MYDB exec msdb.dbo.sp_add_job @job_name= @desc
, @enabled=1
, @notify_level_eventlog=0
, @notify_level_email=0
, @notify_level_netsend=0
, @notify_level_page=0
, @delete_level=0
, @description=@desc
, @category_name='[uncategorized (local)]'
, @owner_login_name='sa' 

/**************************************************/ 
declare @command varchar(255) 
--declare @outputfile varchar (50) 
set @command = 'BACKUP DATABASE [' + @mydb + '] TO DISK = N''E:\MSSQL\Backup\' + @mydb + '.bak'' WITH DESCRIPTION = N''Backup occurs once a day.'', NOFORMAT, NOINIT, NAME = N''' + @mydb + ' Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10' 
--set @outputfile = 'D:\BackupReport\' + @mydb + '.txt' EXEC msdb.dbo.sp_add_jobstep @job_name = @desc
, @step_name=N'Full Backup'
, @step_id=1
, @cmdexec_success_code=0
, @on_success_action=1
, @on_success_step_id=0
, @on_fail_action=2
, @on_fail_step_id=0
, @retry_attempts=0
, @retry_interval=0
, @os_run_priority=0
, @subsystem=N'TSQL'
, @command=@command
, @database_name=@mydb
 --@output_file_name=@outputfile, @flags=2 
go


/****************************************************************************/ /****************************************************************************/ 
-- this trigger will remove the SQL Database Backup job for each database -- that is dropped. /****************************************************************************/ 
--drop trigger DDL_Trig_AutoDeleteBUJob 
--on all server 
--go 

Create trigger DDL_Trig_AutoDeleteBUJob on all server for drop_database as 
-- print 'create database issued.' set nocount on declare @mydb varchar(150) declare @desc varchar (150) set @mydb = CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVarchar(128)) set @desc = 'Full Database Backup ' + @MYDB exec msdb.dbo.sp_delete_job @job_name = @desc

Hope this is helpful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s