MSSQL 备份数据库脚本

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

ALTER     proc BackupDB
  @DatabaseName varchar(100),
  @BackFolder varchar(200),  --备份文件的存放目录 例如：D:\BackUpData\
  @RarFolder varchar(200) --rar.exe文件所在目录
as
declare @FileName varchar(200),@date varchar(30)
set @date=CONVERT(varchar,GETDATE(),112) + REPLACE(convert(varchar(5),getdate(),14),':','')

set @FileName = @BackFolder + @DatabaseName + @date + '.bak'

print @FileName

BACKUP DATABASE @DatabaseName TO DISK = @Filename WITH INIT,NOUNLOAD,COMPRESSION,NAME = N'数据备份',NOFORMAT,NOSKIP,CONTINUE_AFTER_ERROR

print '正在压缩...'
declare @sql varchar(2000)
set @sql=@RarFolder + 'rar.exe a -ep ' + @BackFolder + @DatabaseName + @date + '.rar ' + @FileName
exec master..xp_cmdshell @sql,no_output

print '压缩完毕!'
print '正在删除备份集...'
set @sql='del '+@FileName
exec master..xp_cmdshell @sql,no_output
print '备份集删除完毕!'+char(13)
GO