EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
declare @prefix nvarchar(100),
@datefile nvarchar(100),
@bakfile nvarchar(100),
@rarfile nvarchar(100),
@rarcmd nvarchar(150),
@str_date nvarchar(100),
@sql nvarchar(100)
--设置备份的目录
set @prefix='D:/1/'
set @str_date = replace(replace(replace(convert(varchar(20),getdate(), 120),' ',''),'-',''),':','')
set @datefile = 'aa' +@str_date
set @bakfile = @prefix+@datefile+'.bak'
set @rarfile = @prefix+@datefile+'.rar'
--备份
BACKUP Database aa TO DISK = @bakfile WITH NOFORMAT, NOINIT, NAME = N'aa-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--压缩rar
set @rarcmd ='"c:\Program Files\WinRAR\winrar.exe" ' +'a -df ' +@rarfile+' '+@bakfile
exec master..xp_cmdshell @rarcmd,NO_OUTPUT;
--删除15天之前的备份
set @sql='del d:\1\aa' +rtrim(replace(replace(replace(convert(varchar(20),getdate()-15, 120),' ',''),'-',''),':',''))+'.rar'
exec master..xp_cmdshell @sql,NO_OUTPUT;