[转载]SQL 建立计划任务备份数据库日志文件
自动备份脚本一、用第几次备份来标识的
declare
@physical char(100),
@i int,
@j datetime
begin
select @j=getdate()
select @i=t.id from (select top 1 id from blogger_log order by backupdate desc) as t
while (@i>=0)
begin set @physical ='e:\'+'20'+convert(varchar,@j,12)+convert(varchar,@i)+'.bak'
set @i=@i+1
backup log [xidb] to disk = @physical with noinit,nounload,name=N'xidb backup',skip,stats=10,noformat, no_truncate
insert into blogger_log values(@i,getdate());
print @physical
break
end
end
二、完全用时间来标识的
declare
@physical char(100),
@j datetime
begin
select @j=getdate()
begin set @physical ='e:\'+convert(varchar,datepart(year,@j))+'_'+convert(varchar,datepart(month,@j))+'_'+convert(varchar,datepart(day,@j))+'_'+convert(varchar,datepart(hour,@j))+'_'+convert(varchar,datepart(minute,@j))+'log'+'.bak'
backup log [xidb] to disk = @physical with noinit,nounload,name=N'xidb backup',skip,stats=10,noformat, no_truncate
print @physical
end
end
－－这种方法需要事先建立以日期为名字的文件夹，如20050101，然后20050101这天会会自动备份到该目录下，同时文件名也带有日期标志。
当全部备份到一个文件夹下面也可以。这样就需要改一下@physical
