이 내용은 swynk.com의 SQL Server Script Library의 내용을 정리한 것입니다. 

다음의 문장은 현재 SQL Server에 백업 작업을 수행하는 작업(Job)을 생성하는 구문입니다.　 

/*===================================================================
* usp_Create_MyDB_Backup_Job
* Input Parameters: @DatabaseName , @BackupLocation, @JobName
* Ouput : Creates a Job to backup a given database to the specified location
* Returns 1 on success and -1 on failure
* Description : This procedures just creates a Job for a particular database, however you need to 
schedule this job
* or run the Job manually to actually backup the database.
*
* Written by :Vijaykumar Aski 4/23/2001 Edited by:
====================================================================*/
USE MSDB
go
IF EXISTS (SELECT Name FROM sysobjects WHERE name='usp_Create_MyDB_Backup_Job' and 
type = 'p')
DROP PROC usp_Create_MyDB_Backup_Job
go
CREATE PROCEDURE [dbo].[usp_Create_MyDB_Backup_Job]
--Input parameters
@DatabaseName sysname, --DatabaseName
@BackupLocation varchar(100),-- Folder name where database is to be backedup
@JobName varchar(100) = null -- Not mandatory

as
SET NOCOUNT ON
DECLARE @Ret_Code int
DECLARE @SqlStr varchar(200)
DECLARE @mystepname varchar(200)

set @Ret_code = 1

if not exists(select * from master..sysdatabases where name = ltrim(rtrim(@DatabaseName)))
begin
raiserror('Invalid database name %s :',18,1,@DatabaseName)
set @Ret_Code = -1
end
else -- BACKUP DATABASE slmd TO DISK = ''c:\slmd.dat_bak''',
begin
set @SqlStr = 'BACKUP DATABASE ' + ltrim(rtrim(@DatabaseName))
set @SqlStr = @SqlStr + ' TO DISK = ''' + @BackupLocation + '\' + ltrim(rtrim(@DatabaseName))
set @SqlStr = @SqlStr + cast(datepart(mm,getdate()) as char(2)) +'_'+ cast(datepart(dd,getdate()) 
as char(2))
set @SqlStr = @SqlStr + '.bak'''

set @mystepname= 'Backup ' + ltrim(rtrim(@DatabaseName))+ ' Database'

--Set appropriate job name,if one is not supplied
if @JobName is null
set @JobName = ltrim(rtrim(@DatabaseName)) + '_BackupJob'

--Add the Job in MSDB database
EXEC sp_add_job @job_name = @JobName,
@enabled = 1,
@description = @JobName,
@owner_login_name = 'sa',
@notify_level_eventlog = 3


-- Add job step (backup database).
EXEC sp_add_jobstep @job_name = @JobName,
@step_name = @mystepname,
@subsystem = 'TSQL',
@command = @SqlStr,
@on_success_action = 1,
@retry_attempts = 5,
@retry_interval = 10

end

return @Ret_Code

SET NOCOUNT OFF

go