SQL SERVER 使用作业自动备份及删除过往备份

通过编写SQL脚本,可创建SQL SERVER 数据库的作业将数据库备份到本地或网络共享路径,将备份文件名按日期格式命名,还可根据日期的特点删除特定日期的备份从而达到删除过往备份的功能。本文通过一个实现实例,希望能给大家一个参考。

1.打开 控制台根目录->管理->SQL Server代理->作业 
2.在这里“新建作业”
3.在“常规”页面，输入作业“名称”，分类选择“数据库维护”，选择“所有者”，描述自定
4.在“步骤”页面， “新建步骤”，输入“步骤名”，选择分类“Transact-SQL脚本”并选择你想要创建作

业的数据库，在“命令”输入如下语句：

EXEC sp_configure 'show advanced options', 1    
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure'xp_cmdshell',1      
GO
RECONFIGURE  
GO
declare @sql1 varchar(4000)
declare @sql2 varchar(4000)
declare @backupfile1 varchar(2000) 
declare @backupfile2 varchar(2000) 
declare @retaindays int 
declare @Store varchar(2000) 
declare @IPPart varchar(2000) 
declare @str varchar(100),@dir1 varchar(100),@dir2 varchar(100),@bakfile1 varchar(30),@bakfile2 varchar(30)

set @Store='his'--数据库名

set @IPPart='\\192.147.160.202\backup\'--路径(异地服务器的共享目录，此目录要有有上面用户的访问读写权限)  
set @retaindays=6       --要保留备份的天数

----特别
--EXEC sp_configure 'show advanced options', 1    
-- GO
--RECONFIGURE WITH OVERRIDE
--GO

--创建映射
exec master..xp_cmdshell 'net use \\192.147.160.202\backup "xxxxxx"  /user:QZ-VeeamBK\administrator'


--开始备份

set @backupfile1='D:\his_bak\'+@Store+ '_bak_'+ replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.BAK'  
set @backupfile2= @IPPart+@Store+ + '_bak_'+ replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.BAK'  

set @sql1='backup database '+@Store +' to disk='''+@backupfile1+''' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql1) 
set @sql2='backup database '+@Store +' to disk='''+@backupfile2+''' with  retaindays='+convert(varchar(10),@retaindays)
exec (@sql2) 

--自动删除备份文件
set @dir1='del D:\his_bak\'
set @bakfile1=left(replace(replace(replace(convert(varchar,getdate()-@retaindays,20),'-',''),' ',''),':',''),10)+'*.BAK'
set @str=@dir1 + @Store + '_bak_'  + @bakfile1 
exec xp_cmdshell @str
set @dir2='del \\192.147.160.202\backup\'
set @bakfile2=left(replace(replace(replace(convert(varchar,getdate()-@retaindays,20),'-',''),' ',''),':',''),10)+'*.BAK'
set @str=@dir2 + @Store + '_bak_' + @bakfile2
exec xp_cmdshell @str

--删除映射
--exec master..xp_cmdshell 'net use  /delete' 

--关闭允许执行xp_cmdshell
EXEC sp_configure'xp_cmdshell',0   
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0   
GO
RECONFIGURE WITH OVERRIDE
GO

5.转到“调度”页面，“新建调度”，输入“名称”，选择“调度类型”，这个随你发挥，我在项目中使用反

复出现（因为要定期备份嘛）。
6.到这基本完成一个简单的作业了。然后回到“SQL Server代理”右键“启动”，并启动你所建立的作业。