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