数据库作业通用代码
1 declare @backup nvarchar(500) 2 declare @file nvarchar(500) 3 declare @DeleteFile nvarchar(500) 4 declare datebak cursor for 5 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+'''' 6 ,[DeleteFile]='master.dbo.xp_delete_file 0,N''F:\DabaBaseBackup\BU2\'+Name+''',N''bak'',N'''+convert(varchar(100),DateAdd(Day,-10,GETDATE()),126)+'''' 7 from master..sysdatabases where Name like 'KR_%' 8 open datebak 9 fetch next from datebak into @backup,@file,@DeleteFile 10 while @@fetch_status=0 11 begin 12 EXECUTE (@DeleteFile) 13 EXECUTE (@file) 14 EXECUTE (@backup) 15 fetch next from datebak into @backup,@file,@DeleteFile 16 end 17 close datebak 18 deallocate datebak 19 GO
删除数据库作业
1 use [msdb] 2 declare @job_name varchar(100) 3 set @job_name = N'job_name' 4 5 --注：job_name为维护计划对应的job name 6 7 --删除在计划里面的日志 8 9 delete sysmaintplan_log from sysmaintplan_subplans as subplans 10 inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id 11 inner join sysmaintplan_log on subplans.subplan_id =sysmaintplan_log.subplan_id 12 where (syjobs.name = @job_name) 13 14 --删除代理的作业 15 16 delete sysjobschedules from sysjobs_view v 17 inner join sysjobschedules o on v.job_id=o.job_id where v.name=@job_name 18 19 --删除子计划 20 21 delete sysmaintplan_subplans from sysmaintplan_subplans as subplans 22 inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id 23 where (syjobs.name = @job_name) 24 25 --删除作业 26 27 delete from msdb.dbo.sysjobs_view where name = @job_name
