Sqlserver2005自动定期备份、Rar压缩并删除过期备份

Sqlserver2005自动定期备份、压缩并删除过期备份

一、应用场景

登陆身份：windows身份验证或者 SQL server 身份验证

备份文件路径：E:\DataBackup

备份说明：备份一个月的数据库，然后打包压缩，删除一个月前的备份文件。

 

二、准备工作

1、启动xp_cmdshell 

xp_cmdshell 扩展存储过程将命令字符串作为操作系统命令 shell 执行，并以文本行的形式返回所有输出。由于xp_cmdshell 可以执行任何操作系统命令，所以一旦SQL Server管理员帐号（如sa）被攻破，那么攻击者就可以利用xp_cmdshell 在SQL Server中执行操作系统命令，如：创建系统管理员，也就意味着系统的最高权限已在别人的掌控之中。由于存在安全隐患，所以在SQL Server 2005中， xp_cmdshell 默认是关闭的。

法一：通过新建查询实现

--开启xp_cmdshell部分
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

--通过xp_cmdshell执行shell命令的部分
--------------------------------------------------
Exec xp_cmdshell 'bcp '
GO

--关闭xp_cmdshell部分
-----------------------------------------------------
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

法二：通过SQL server外围应用配置器来实现

1、 点击功能的外围应用配置器

2、选择xp_cmdshell 勾选启动xp_cmdshell，点应用就可以了。

2、 配置WinRAR压缩环境变量

将WinRAR执行文件路径放到环境变量就可以了。点击我的电脑属性->高级->系统环境变量->将执行文件路径放上去

 

 

3、  启动SQL Server代理

 

三、建立作业计划

1、备份一个月的数据库

T-SQL

-----------自动备份一个月的数据库，自动创建文件夹

declare  @sqlPath  varchar(100), ---数据路径

@CubbyholePath varchar(100), ---文件夹名称

@sqlStartDate varchar(100)--本月开始时间

set @sqlStartDate=convert(varchar(10),dateadd(d,-day(getdate())+1,getdate()),120) --得到本月开始时间

set @CubbyholePath=case convert(varchar(10),getdate(),120)

 when @sqlStartDate

 then

'E:\DataBackup\Test'+@sqlStartDate

 ELSE--得到文件名

 'E:\DataBackup\Test'+@sqlStartDate

end

set @sqlPath=@CubbyholePath+'\Test'+rtrim(convert(varchar,getdate(),112))+'.bak'--备份bak文件完整路径

if(convert(varchar(10),getdate(),120)=@sqlStartDate)--判断是否为本月第一天，是则创建一个新的文件架

begin

set @CubbyholePath='mkdir '+@CubbyholePath

exec xp_cmdshell @CubbyholePath--' madik E:\DataBackup\'+convert(varchar(10),getdate(),120)--如果为当月的第一天，创建一个文件夹

end

backup  database  Test to  disk=@sqlPath

go

 2、步骤

 新建作业：

 

新建步骤：

将刚刚的脚本复制到命令里面

 

 

新建计划

点确定，自动备份完成。

 

 

2、自动压缩一个月的bak文件，删除过期备份

T-SQL

------------自动压缩一个月的bak文件

declare @sqlPath  varchar(100), ---数据路径

@CubbyholePath varchar(100) ---文件夹路径

set @CubbyholePath='E:\DataBackup\Test'+convert(varchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),120)

set @sqlPath='RAR.EXE A -R '+@CubbyholePath+'.rar '+'E:\DataBackup\Test'+convert(varchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),120)

exec xp_cmdshell @sqlPath

go

 

-----------------自动删除文件夹

declare  @sqlPath  varchar(100), ---数据路径

@CubbyholePath varchar(100)---文件夹名称

set @CubbyholePath='E:\DataBackup\Test'+convert(varchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),120)

set @sqlPath='RD/s/q '+@CubbyholePath

exec xp_cmdshell @sqlPath

go

 

建立作业计划，将脚本复制过去就可以了。

需要注意的是步骤里面分两步，先压缩，后备份。

创建计划，这个我每个月的第一天执行一次。

点击确定，作业创建完成。

四、测试作业

注意：因为本人的方法是根据每个月第一天创建文件夹，如果当天不是本月第一天，先手动创建文件夹，文件夹名称：Test2013-07-01

1、 测试备份功能

修改系统时间，测试备份功能

成功！

 

2、 测试压缩删除功能

调整时间为8月1号1.09分

测试成功！