SQL Server自動(dòng)備份、壓縮和刪除壓縮

mic0601

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)

        

--設(shè)置備份的目錄      

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-完整 數(shù)據(jù)庫 備份', 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;