SQLServer自动备份、压缩、删除旧文件

作为者数据库维护人员，可能大家经常要做的事情就是备份数据库，并且为了安全起见最好是异地备份，但是如果要经常备份，然后把他们传到本地，是一件非常费时费力的事情，尤其像我这种比较懒的程序员，甚至有时候会忘记备份，所以比较好的解决办法就是让SQL Server自动备份，同时自动下载。同时由于SQL Server自动备份的bak文件通常都比较大，如果直接传到本地，要花比较久的时间，因此为了提高效率最好在备份后先压缩，并且为了安全起见，下载下来的文件还要加密。那么如何让SQL自动备份、加密压缩、删除旧的BAK文件并自动下载的所有流程都自动完成呢，下面我就将我使用到的工具、方法和代码共享出来，供大家参考。

第一步：在查询分析器中执行以下命令

use master

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

SQL Server2000可以跳过这一步，而对于SQL Server2005因为它默认是不支持xp_cmdshell执行命令的，SQL Server 已封锁元件 'xp_cmdshell' 的程序 'sys.xp_cmdshell' 的存取，此元件已经由此伺服器的安全性组态关闭，系统管理员可以使用sp_configure来启用 'xp_cmdshell' 的使用，所以我们得恢复其执行命令。

第二步：在SQL企业管理器—>SQL代理（如果服务未启用，先启用该服务）—>作业—>新建作业

①在常规选项中输入作业名称如图1：

图1

②录入作业步骤：在步骤选项中新建作业步骤1，名称命名为‘XX备份、压缩’，然后在命令框中输入如下的SQL 代码：

declare

@prefix nvarchar(100),

@datefile nvarchar(100),

@bakfile nvarchar(100),

@rarfile nvarchar(100),

@delcmd nvarchar(100),

@rarcmd nvarchar(150)

set @prefix='F:\ERP\'--备份文件夹

set @datefile='DB_AAA'+cast(Year(GetDate())as varchar(4))+cast(Month(GetDate())as varchar(2))+cast(Day(GetDate())as varchar(2))+

CAST(DATEPART(Hour,GETDATE())AS nvarchar(2))+CAST(DATEPART(minute,GETDATE())AS nvarchar(2))--备份文件名

set @bakfile=@prefix+@datefile+'.bak'

set @rarfile=@prefix+@datefile+'.rar'

BACKUP DataBASE DB_AAA TO DISK= @bakfile WITH INIT , NOUNLOAD ,NAME= N'DB_AAA数据备份

', NOSKIP , STATS = 10, NOFORMAT

set @delcmd ='del'+@prefix+'RAR\*.rar'--先清空RAR备份文件夹

set @rarcmd='C:\Progra~1\WinRAR\WinRAR.exe a -p123 '+@rarfile+' '+@bakfile --将yourpw改为您要设置的密码，进行加密压缩

exec master..xp_cmdshell @delcmd

exec master..xp_cmdshell @rarcmd

作业步骤1建好后，新建作业步骤2，名称命名为‘删除当天BAK文件’，然后在命令框中输入如下的SQL代码：declare @d Nvarchar(64);

set @d =convert(varchar,dateadd(day,-0,getdate()),120);

EXECUTE master.dbo.xp_delete_file 0,N'F:\ERP',N'bak',@d;

新建计划任务：在计划选项中新建一个计划任务如下图：

作业计划建好后在SQL企业管理器-->SQL代理-->作业

在作业上点右键查看该作业是否已经启用。