这里的w:\为路径 这是偶几天写的,针对几十个数据库自动备份、删除4天前的备份(由于周六日休息,所以用del1,2,3,4删除以4天前5天前6前的备份) 用以下SQL调度 declare @s nvarchar(4000),@s2 nvarchar(4000),@del nvarchar(4000),@del2 nvarchar(4000), @del3 nvarchar(4000),@del4 nvarchar(4000),@del5 nvarchar(4000),@del6 nvarchar(4000) select @s='',@s2='',@del='',@del2='',@del3='',@del4='',@del5='',@del6='' select @s= @s+ case when dbid !>31 then char(13)+'backup database '+quotename(Name)+' to disk =''w:\'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak'' with init' else '' end, @s2=@s2+ case when dbid >31 then char(13)+'backup database '+quotename(Name)+' to disk =''w:\'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak'' with init' else '' end, @del=@del+ case when dbid !>31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-4,112)+'.bak'', no_output' else '' end, @del2=@del2+ case when dbid >31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-4,112)+'.bak'', no_output' else '' end, @del3=@del3+ case when dbid !>31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-5,112)+'.bak'', no_output' else '' end, @del4=@del4+ case when dbid >31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-5,112)+'.bak'', no_output' else '' end, @del5=@del5+ case when dbid !>31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-6,112)+'.bak'', no_output' else '' end, @del6=@del6+ case when dbid >31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-6,112)+'.bak'', no_output' else '' endfrom master..sysdatabases where dbid>4 order by dbid ascexec (@del+@del2+@del3+@del4+@del5+@del6)exec(@s+@s2)
这是偶几天写的,针对几十个数据库自动备份、删除4天前的备份(由于周六日休息,所以用del1,2,3,4删除以4天前5天前6前的备份)
用以下SQL调度
declare @s nvarchar(4000),@s2 nvarchar(4000),@del nvarchar(4000),@del2 nvarchar(4000),
@del3 nvarchar(4000),@del4 nvarchar(4000),@del5 nvarchar(4000),@del6 nvarchar(4000)
select @s='',@s2='',@del='',@del2='',@del3='',@del4='',@del5='',@del6=''
select
@s= @s+
case when dbid !>31 then char(13)+'backup database '+quotename(Name)+' to disk =''w:\'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak'' with init' else '' end,
@s2=@s2+
case when dbid >31 then char(13)+'backup database '+quotename(Name)+' to disk =''w:\'+Name+'_'+convert(varchar(8),getdate(),112)+'.bak'' with init' else '' end,
@del=@del+
case when dbid !>31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-4,112)+'.bak'', no_output' else '' end,
@del2=@del2+
case when dbid >31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-4,112)+'.bak'', no_output' else '' end,
@del3=@del3+
case when dbid !>31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-5,112)+'.bak'', no_output' else '' end,
@del4=@del4+
case when dbid >31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-5,112)+'.bak'', no_output' else '' end,
@del5=@del5+
case when dbid !>31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-6,112)+'.bak'', no_output' else '' end,
@del6=@del6+
case when dbid >31 then char(13)+'exec master..xp_cmdshell '' del w:\'+Name+'_'+convert(varchar(8),getdate()-6,112)+'.bak'', no_output' else '' endfrom master..sysdatabases where dbid>4 order by dbid ascexec (@del+@del2+@del3+@del4+@del5+@del6)exec(@s+@s2)