以前寫的一個按照所屬星期不同 進行不同方式備份的東西,且星期一做完全備份並刪除上星期備份 ,你參考下嘛,,, 改寫一下做成存儲過程,,,應該就行了根据日期自动判断备份类型exec sp_configure 'show advanced options',1 --開啓高級設置 reconfigure WITH OVERRIDE; exec sp_configure 'xp_cmdshell',1 reconfigure WITH OVERRIDE;exec sp_configure 'show advanced options',0 --開啓高級設置 reconfigure WITH OVERRIDE; declare @nowdate varchar(10) declare @i as int set @nowdate= getdate() declare @bdchardate varchar(8) declare @delchardate varchar(8) declare @sqlName nvarchar(100) --获得当前星期 set @i=(case when DATEPART(Weekday,@nowdate)=1 then 7 else DATEPART(Weekday,@nowdate)-1 end) --获得本周星期一 set @nowdate=convert(varchar(10),dateadd(day,-(@i-1),@nowdate),111) --获得本周星期一作为生成辈份文件名 set @bdchardate= cast(year(@nowdate) as varchar(4))+cast(right('00'+cast(month(@nowdate)as varchar(2)),2) as varchar(2))+cast(right('00'+cast(day(@nowdate)as varchar(2)),2) as varchar(2)) --获得上周星期一 set @nowdate=convert(varchar(10),dateadd(day,-(@i-1+7),@nowdate),111) --获得上周星期一作为需要删除的文件名 set @delchardate= cast(year(@nowdate) as varchar(4))+cast(right('00'+cast(month(@nowdate)as varchar(2)),2) as varchar(2))+cast(right('00'+cast(day(@nowdate)as varchar(2)),2) as varchar(2)) --select getdate(),@i,@bdchardate,@delchardate set @sqlName='' if @i=1 begin --完全备份当周文件 set @sqlName=' backup database testDB to disk=''E:\H_BACKUP'+@bdchardate+'.bak'' with init ' exec(@sqlName)
--判断上周备份文件是否存在,存在则删除 IF OBJECT_ID('tempdb..#tb') IS NOT NULL BEGIN drop table #tb END create table #tb(a bit,b bit,c bit) set @sqlName=' insert into #tb exec master..xp_fileexist ''del E:\H_BACKUP'+@delchardate+'.bak'' ' exec (@sqlName) if exists(select 1 from #tb where a=1) begin set @sqlName=' exec master..xp_cmdshell ''del E:\H_BACKUP'+@delchardate+'.bak'' ' exec (@sqlName) end drop table #tb end if @i>=2 and @i<=5 begin set @sqlName='backup database testDB to disk=''E:\H_BACKUP'+@bdchardate+'.bak'' WITH DIFFERENTIAL ' exec (@sqlName) end
,你參考下嘛,,,
改寫一下做成存儲過程,,,應該就行了根据日期自动判断备份类型exec sp_configure 'show advanced options',1 --開啓高級設置
reconfigure WITH OVERRIDE; exec sp_configure 'xp_cmdshell',1
reconfigure WITH OVERRIDE;exec sp_configure 'show advanced options',0 --開啓高級設置
reconfigure WITH OVERRIDE;
declare @nowdate varchar(10)
declare @i as int
set @nowdate= getdate()
declare @bdchardate varchar(8)
declare @delchardate varchar(8)
declare @sqlName nvarchar(100)
--获得当前星期
set @i=(case when DATEPART(Weekday,@nowdate)=1 then 7 else DATEPART(Weekday,@nowdate)-1 end)
--获得本周星期一
set @nowdate=convert(varchar(10),dateadd(day,-(@i-1),@nowdate),111)
--获得本周星期一作为生成辈份文件名
set @bdchardate= cast(year(@nowdate) as varchar(4))+cast(right('00'+cast(month(@nowdate)as varchar(2)),2) as varchar(2))+cast(right('00'+cast(day(@nowdate)as varchar(2)),2) as varchar(2))
--获得上周星期一
set @nowdate=convert(varchar(10),dateadd(day,-(@i-1+7),@nowdate),111)
--获得上周星期一作为需要删除的文件名
set @delchardate= cast(year(@nowdate) as varchar(4))+cast(right('00'+cast(month(@nowdate)as varchar(2)),2) as varchar(2))+cast(right('00'+cast(day(@nowdate)as varchar(2)),2) as varchar(2))
--select getdate(),@i,@bdchardate,@delchardate
set @sqlName=''
if @i=1
begin
--完全备份当周文件
set @sqlName=' backup database testDB to disk=''E:\H_BACKUP'+@bdchardate+'.bak'' with init '
exec(@sqlName)
--判断上周备份文件是否存在,存在则删除
IF OBJECT_ID('tempdb..#tb') IS NOT NULL
BEGIN
drop table #tb
END
create table #tb(a bit,b bit,c bit)
set @sqlName=' insert into #tb exec master..xp_fileexist ''del E:\H_BACKUP'+@delchardate+'.bak'' '
exec (@sqlName)
if exists(select 1 from #tb where a=1)
begin
set @sqlName=' exec master..xp_cmdshell ''del E:\H_BACKUP'+@delchardate+'.bak'' '
exec (@sqlName)
end
drop table #tb
end
if @i>=2 and @i<=5
begin
set @sqlName='backup database testDB to disk=''E:\H_BACKUP'+@bdchardate+'.bak'' WITH DIFFERENTIAL '
exec (@sqlName)
end