数据库作业通用代码

declare @backup nvarchar(500)
declare @file nvarchar(500)
declare @DeleteFile nvarchar(500)
declare datebak cursor for
select [bak]='backup database  '+quotename(Name)+'  to disk =''F:\DabaBaseBackup\BU2\'+Name+'\'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak''  with init',[file]='master.dbo.xp_create_subdir N''F:\DabaBaseBackup\BU2\'+Name+''''
,[DeleteFile]='master.dbo.xp_delete_file 0,N''F:\DabaBaseBackup\BU2\'+Name+''',N''bak'',N'''+convert(varchar(100),DateAdd(Day,-10,GETDATE()),126)+''''
from master..sysdatabases  where Name like 'KR_%'
open datebak
fetch next from datebak into @backup,@file,@DeleteFile
while @@fetch_status=0
    begin
        EXECUTE (@DeleteFile)
        EXECUTE (@file)
        EXECUTE (@backup)
        fetch next from datebak into @backup,@file,@DeleteFile
    end
close datebak
deallocate datebak
GO

 删除数据库作业

use [msdb]
 declare @job_name varchar(100)
 set @job_name = N'job_name'

--注：job_name为维护计划对应的job name  

--删除在计划里面的日志

delete sysmaintplan_log from sysmaintplan_subplans as subplans
 inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id
 inner join sysmaintplan_log on subplans.subplan_id =sysmaintplan_log.subplan_id
 where (syjobs.name = @job_name)

--删除代理的作业

delete sysjobschedules from sysjobs_view v
 inner join sysjobschedules o on v.job_id=o.job_id where v.name=@job_name

--删除子计划

delete sysmaintplan_subplans from sysmaintplan_subplans as subplans
 inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id
 where (syjobs.name = @job_name)

--删除作业

delete from msdb.dbo.sysjobs_view where name = @job_name