Create the SQL for the job

Create backup scripts for backups mydb and delete backups up to 5 days ago

use msdb ; goexec dbo.sp_add_job     @job_name  =  ' job_backup_db ', name of   --job      @enabled  = 1, --Indicates the status of the added job (default is (enabled))     @ description =  ' database regular backup Job! ', description of  --job      @owner_login_name  =  ' sa ',  --the login name of the owning job      @notify_level_eventlog  = 2, --0  never succeeds (the default) after the failure always      @notify_level_email  = 2, --Default value, indicating never send      @notify_ level_netsend = 2, --default value, indicating never send      @notify_level_page  = 2,  --The default value, which indicates the category never sent      @category_id =3,    --job id    @ delete_level = 0 ; --default is, not deleted, to indicate when jobs are deleted gouse msdb ; goexec sp_add_jobstep     @job_name  =  ' job_backup_db ', name of the job  --step       @step_name  =  ' Job_backup_db_step ',  --step name     @[email protected] @servername,--server name       @database_name = ' master ',--database name      @subsystem  =  ' TSQL ',  -- transact-sql  Statement \ Tab--char (9)   newline character char (Ten)   Enter char (all)       @command   = '/***** full backup ******/declare  @Path_MYDB  nvarchar (+), @Path_MYDB_log  nvarchar (+) declare  @olddate  datetimeSet  @Path_MYDB  = "D:\DataBak\MYDB" +convert (NVARCHAR), GETDATE (), (+) + ". Bak ' set  @Path_MYDB_log  = ' D:\DataBak\MYDB_log ' +convert (NVARCHAR), GETDATE (), () + ". Bak"--print   @bak_nameUSE  masterBackup Database [MYDB] to [email protected]_MYDB; Backup log [mydb] to [email protected] @Path_MYDB_logUse  [MYDB]DBCC  shrinkfile  (N ' Mydb_log '  , 0, truncateonly);select  @olddate =getdate () -5execute  master.dbo.xp_delete_file&Nbsp;0,n ' D:\DataBak\ ', N ' bak ', @olddate ',  --add sql     to execute @retry_attempts  =  2,--The number of retries      @retry_interval  = 0.05 ; --When this step fails Interval between two retries (minutes) gouse msdb ;    goexec sp_add_schedule      @schedule_name  =  ' Job_backup_db_plan '  ,--program name      @freq_type  =  4 , --indicates that the job execution time is a value of one time, for each day (, 8,16,32, etc. reference Help document) value for the secondary      @freq_interval  =  1,--The number of days to execute the job description Ibid      @active_start_time The  = 010000 ;--data type is int, the default value is, Morning of the indicator Hour: 00:00, and must be entered using the hhmmss  format goexec sp_attach_schedule    @job_name  =  ' job_backup_db ',  --the name of the scheduled job     @schedule_name  =  ' Job_backup_db_plan '  ; - -The name of the schedule for the job setting gouse msdb ; goexec dbo.sp_add_jobserver     @job_name  =  ' job_backup_db ', name of  --job      @server_name  = @ @servername  ; --The name of the target server for the job go 

This article is from the "Dead Leaf" blog, please make sure to keep this source http://mydbs.blog.51cto.com/513727/1745284

Create the SQL for the job