Create Backup Jobs automatically via DDL Triggers

Last Post 29 Nov 2011 07:45 AM by sqladmin. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqladmin
New Member
New Member

--
29 Nov 2011 07:36 AM
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
sqladmin
New Member
New Member

--
29 Nov 2011 07:45 AM
by the way.... if you want to use other backup methodologies, or scripts such as LiteSpeed or what have you... might want to check out the following post:

http://www.sqlmag.com/forums/aft/81285

hope this is useful.
You are not authorized to post a reply.

Acceptable Use Policy