备份数据库并生成Rar
Code------------------------------------
---备份数据库
---参数:备份文件夹地址,数据库名称 EXEC dbbackup 'D:\dbbak\','ihpmp'
---注意事项:
---1.需建好数据库文件夹,和rar文件夹
---2.启动xp_cmdshell权限在SQL Server 外围应用配置器配置
---------------------------------------
CREATE PROCEDURE [dbo].[dbbackup]
@prefix nvarchar(100),--备份文件夹
@dbname nvarchar(100) --数据库名
AS
DECLARE
@datefile nvarchar(100),
@bakfile nvarchar(100),
@rarfile nvarchar(100),
@delcmd nvarchar(100),
@rarcmd nvarchar(150),
@bakname nvarchar(150)
SET @bakname=@dbname+'数据备份'
set @datefile=@dbname+cast(Year(GetDate()) as varchar(4))+cast(Month(GetDate()) as varchar(2))+cast(Day(GetDate()) as varchar(2))
set @bakfile=@prefix+@datefile+'.bak'
set @rarfile=@prefix+'RAR\'+@datefile+'.rar'
BACKUP DataBASE @dbname TO DISK = @bakfile WITH INIT , NOUNLOAD , NAME = @bakname, 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
