转载自品略图书馆 http://www.pinlue.com/article/2020/05/1521/2310518365724.html
SQL Server备份脚本:
脚本周三全备份,其他时间差异备份。
declare @device varchar(30);
declare @filename varchar(50);
declare @datetime varchar(50);
declare @weekname varchar(50);
declare @weeknametmp varchar(4);
declare @weekday int;
set @weekname=datename(weekday,getdate());
set @datetime = convert(varchar(20),getdate(),112);
set @device= "RapooOA" + @datetime;
set @filename="D:\SQLBackup\RapooOA"+@datetime+".bak";
set @weeknametmp=substring(@weekname,3,1);
if (@weeknametmp="一")
set @weekday=1;
else if (@weeknametmp="二")
set @weekday=2;
else if (@weeknametmp="三")
set @weekday=3;
else if (@weeknametmp="四")
set @weekday=4;
else if (@weeknametmp="五")
set @weekday=5;
else if (@weeknametmp="六")
set @weekday=6;
else if (@weeknametmp="日")
set @weekday=0;
set @datetime = convert(varchar(20),getdate()-@weekday,112);
set @device= "RapooOA" + @datetime;
set @filename="D:\SQLBackup\RapooOA"+@datetime+".bak"
if (@weekname="星期三")
begin
execute sp_addumpdevice "disk",@device,@filename;
backup database RapooOA to @device
end
else
begin
backup database RapooOA to @device with differential
end
保存为.sql格式的文件,使用计划任务定时执行就可以了。
如果出现下列错误:只要执行 exec sp_dropdevice RapooOA20160117 就可以删除了,然后重新运行就可以。