针对备份进行压缩

点击(此处)折叠或打开

USE [master]
GO
/****** Object: StoredProcedure [dbo].[FullBackup] Script Date: 03/16/2017 17:21:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[USP_CYUDBA_FullBackup]
@DBNames varchar(800), --需要备份的数据库名字列表，以逗号分割多个数据库
@BaseDest varchar(300), --备份文件存放根目录
@BackupKeepDays varchar(30) --备份文件保留天数
AS
DECLARE @FileName_Full varchar(600) --备份文件名
DECLARE @BackupSql varchar(7000) --执行备份语句
DECLARE @Dest varchar(600) --备份文件存放目录
DECLARE @Mkdir varchar(600) --创建文件夹语句
DECLARE @Rmfile varchar(600) --删除过期备份语句
DECLARE @ServerName varchar(30) --计算机名字
DECLARE @DBName varchar(30) --备份数据库名字,临时变量
DECLARE @DT varchar(300) --备份日期
DECLARE @DateTime varchar(300) --备份时间
DECLARE @HH varchar(2)
DECLARE @MI varchar(2)
DECLARE @db_i int --多个数据库名是确认第一个逗号分割的位置
DECLARE @Qt char(1)
DECLARE @ZIPFILE varchar(600)
DECLARE @ZIPFILE_DATA varchar(600)
DECLARE @ZIPFILE_LOG varchar(600)
SET @HH = DATEPART ( Hh , GETDATE())
SET @MI = DATEPART ( Mi , GETDATE())
SET @ServerName = @@SERVERNAME
IF ( LEN(@HH) < 2 )
BEGIN
SET @HH = '0'+ @HH
END
IF ( LEN(@MI) < 2 )
BEGIN
SET @MI = '0'+ @MI
END
set @Dest = @BaseDest
IF ( CHARINDEX ( '\', @Dest, LEN ( @Dest ) ) <> LEN ( @Dest ) )
BEGIN
SET @Dest = @Dest + '\' --如果根目录结尾无\则手动添加一个
END
SET @DT = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','')
SET @DateTime = @DT + '_' + @HH + @MI
WHILE ( LEN ( @DBNames ) > 1 )
BEGIN
SET @BackupSql=''
IF ( SUBSTRING ( @DBNames, 1 , 1 ) = ',' )
BEGIN
SET @DBNames = SUBSTRING ( @DBNames , 2 , LEN(@DBNames) )
END
IF ( CHARINDEX ( ',', @DBNames ) > 0)
BEGIN
SET @db_i = CHARINDEX ( ',' , @DBNames )
SET @DBName = SUBSTRING ( @DBNames , 1 , @db_i - 1 )
SET @DBNames = SUBSTRING ( @DBNames , @db_i , LEN(@DBNames) )
END
IF ( CHARINDEX ( ',', @DBNames ) = 0)
BEGIN
SET @DBName = @DBNames
SET @DBNames = ''
END
SET @FileName_Full = @DBName + '_Full_BAK_' + @DateTime + '.bak'
SET @Qt = ''''
DECLARE @Dest_Now varchar(600)
SET @Dest_Now = @Dest
IF ( @DBName = 'master' or @DBName = 'msdb' or @DBName = 'temp' or @DBName = 'model' or @DBName = 'resource' or @DBName = 'tempdb' or @DBName = 'mssqlsystemresource' )
BEGIN
SET @Dest_Now = @Dest +'SYS_DBBAK' + '\' + @DT
SET @Mkdir = 'mkdir -p ' + @Dest_Now
SET @Rmfile = 'forfiles /p ' + @BaseDest + ' /m *_Full_BAK_*.zip* /S -d -' + @BackupKeepDays + ' /c "cmd /c del /f @path" & forfiles /p ' + @BaseDest + ' /S -d -1 /c "cmd /c if @isdir==TRUE rd @path" '
SET @BackupSql = 'BACKUP DATABASE [' + @DBName + '] TO DISK = ' + @Qt + @Dest_Now + '\' + @FileName_Full + @Qt + ' WITH INIT ;'
SET @ZIPFILE = 'cd ' + @Dest_Now + ' & RAR.EXE A -R -DF '+ @FileName_Full +'.zip ' + @FileName_Full
IF ( LEN ( @DBName ) > 0 )
BEGIN
EXEC master.dbo.xp_cmdshell @Mkdir
EXEC (@BackupSql)
EXEC master.dbo.xp_cmdshell @ZIPFILE
EXEC master.dbo.xp_cmdshell @Rmfile
END
END
ELSE
BEGIN
DECLARE @FileName_Log varchar(1000)
SET @FileName_Log = @DBName + '_LOG_BAK_' + @DateTime + '.bak'
SET @Dest_Now = @Dest +'USER_DBBAK' + '\' + @DT
SET @Rmfile = 'forfiles /p ' + @BaseDest + ' /m *_Full_BAK_*.bak* /S -d -' + @BackupKeepDays + ' /c "cmd /c del /f @path" & forfiles /p ' + @BaseDest + ' /S -d -1 /c "cmd /c if @isdir==TRUE rd @path" '
SET @Mkdir = 'mkdir -p ' + @Dest_Now + '\LOG & mkdir -p ' + @Dest_Now + '\FULL'
SET @BackupSql = ''
SET @BackupSql = @BackupSql + 'BACKUP DATABASE [' + @DBName + '] TO DISK = ' + @Qt + @Dest_Now + '\FULL\' + @FileName_Full + @Qt + ' WITH INIT ;'
SET @BackupSql = @BackupSql + 'if exists(select 1 from sys.databases where name=''' + @DBName + ''' and recovery_model_desc!=''SIMPLE'')'
SET @BackupSql = @BackupSql + 'BEGIN BACKUP LOG [' + @DBName + '] TO DISK = ' + @Qt + @Dest_Now + '\LOG\' + @FileName_Log + @Qt + ' WITH INIT ;'
SET @BackupSql = @BackupSql + 'USE [' + @DBName + '];DECLARE @DBName_Log varchar(100); DECLARE DBName_Logs CURSOR for SELECT name FROM sys.database_files where type=1;'
SET @BackupSql = @BackupSql + 'OPEN DBName_Logs;fetch next from DBName_Logs into @DBName_Log;while @@fetch_status<>-1 '
SET @BackupSql = @BackupSql + 'BEGIN DBCC SHRINKFILE ( @DBName_Log ,TRUNCATEONLY) ;fetch next from DBName_Logs into @DBName_Log;'
SET @BackupSql = @BackupSql + 'END;close DBName_Logs;deallocate DBName_Logs;END;'
SET @ZIPFILE_LOG = 'cd ' + @Dest_Now +'\LOG\' + ' & RAR.EXE A -R -DF '+ @FileName_Log +'.zip ' + @FileName_Log
SET @ZIPFILE_DATA = 'cd ' + @Dest_Now +'\FULL\' + ' & RAR.EXE A -R -DF '+ @FileName_Full +'.zip ' + @FileName_Full
IF ( LEN ( @DBName ) > 0 )
BEGIN
EXEC master.dbo.xp_cmdshell @Mkdir
EXEC (@BackupSql)
EXEC master.dbo.xp_cmdshell @ZIPFILE_DATA
EXEC master.dbo.xp_cmdshell @ZIPFILE_LOG
EXEC master.dbo.xp_cmdshell @Rmfile
END
END
END

Scripts:SQLServer备份压缩