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',?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:DataBackupTest'+@sqlStartDate

?ELSE--得到文件名

?'E:DataBackupTest'+@sqlStartDate

end

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

if(convert(varchar(10),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:DataBackupTest'+convert(varchar(10),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,getdate())),120)

set @sqlPath='RAR.EXE A -R '+@CubbyholePath+'.rar '+'E:DataBackupTest'+convert(varchar(10),120)

exec xp_cmdshell @sqlPath

go

?

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

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

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

set @CubbyholePath='E:DataBackupTest'+convert(varchar(10),120)

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

exec xp_cmdshell @sqlPath

go

?

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

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

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

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

四、测试作业

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

1、 测试备份功能

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

成功！

?

2、 测试压缩删除功能

调整时间为8月1号1.09分

测试成功！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!