SQL SERVER 备份压缩(Datacenter 或者 Enterprise 版本分多个文件备份)

USE [IPVA_Builder]
GO
/****** Object:  StoredProcedure [dbo].[usp_BackupCompression]    Script Date: 10/26/2017 09:04:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	备份文件压缩机制
-- EXEC [usp_BackupCompression] 'D:\中文\234\'
-- =============================================
ALTER PROCEDURE [dbo].[usp_BackupCompression]
	@path NVARCHAR(2000) --备份路径
AS
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
	
DECLARE @SQL NVARCHAR(max)
DECLARE @SQL1 NVARCHAR(max)
DECLARE @DB_DataBaseName NVARCHAR(200)
DECLARE @Spath NVARCHAR(2000)

set @Spath='MD '+@path+''
--创建文件夹
exec master..xp_cmdshell @Spath
--创建主库及分库
CREATE TABLE #TableName
(
	ID INT IDENTITY(1,1),
	TableName NVARCHAR(100)
)
INSERT INTO #TableName SELECT DB_NAME()

INSERT INTO #TableName 
SELECT name FROM Master..SysDatabases WHERE NAME LIKE DB_NAME() + '_SUB_%' AND LEN(Replace(name,DB_NAME(),''))=9 

DECLARE @t INT ,@tcount INT
SELECT @tcount=COUNT(*) FROM #TableName
SET @t =1
WHILE(@t<=@tcount)
BEGIN
	SELECT @DB_DataBaseName=TableName FROM #TableName WHERE ID=@t
	DECLARE @database_size DECIMAL(19,2)
	CREATE TABLE #database_size
	(
		database_size DECIMAL(19,2)
	)
	--获取数据库内存大小
	DECLARE @SQL3 NVARCHAR(MAX)
	SET @SQL3='
	SELECT  ltrim(str((convert (dec (15,2),dbsize) + convert (dec (15,2),logsize))   * 8192 / 1048576,15,2)) database_size
	from (
	select  sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  dbsize
	  ,  sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))  logsize
	  from ['+@DB_DataBaseName+'].dbo.sysfiles 
	  ) a '
    INSERT INTO #database_size EXEC(@SQL3)
    SELECT @database_size=database_size FROM #database_size
	--数据库大于50G	
	IF(CAST(@database_size AS DECIMAL(19,2))>50*1024)
	BEGIN
		IF(CHARINDEX('Datacenter',@@VERSION)>0 OR CHARINDEX('Enterprise',@@VERSION)>0)--是否包含Datacenter 或者 Enterprise 版本
		BEGIN
			--大于0包含
			DECLARE @i INT 
			DECLARE @count INT 
			SET @SQl='BACKUP DATABASE '+@DB_DataBaseName+'  TO '
			SET @count=ceiling(@database_size/1024/50)
			SET @i=1
			WHILE @i<=@count
			BEGIN
			   SET @SQl=@SQl+'DISK='''+@path+''+@DB_DataBaseName+CAST(@i AS NVARCHAR(10))+'.bak'','
			   PRINT @SQl
			   SET @i=@i+1
			END
			SET @SQl=LEFT(@SQl,LEN(@SQl)-1)
			SET @SQL=@SQL+' with STATS = 1,compression;--压缩备份 '
			--PRINT @SQL
			EXEC(@SQL)
		END
		ELSE
		BEGIN
			--小于0不包含
			DECLARE @k INT 
			DECLARE @kcount INT 
			SET @SQl1='BACKUP DATABASE '+@DB_DataBaseName+'  TO '
			SET @kcount=ceiling(@database_size/1024/50)
			SET @k=1
			WHILE @k<=@kcount
			BEGIN
			   SET @SQl1=@SQl1+'DISK='''+@path+''+@DB_DataBaseName+CAST(@k AS NVARCHAR(10))+'.bak'','
			   SET @k=@k+1
			END
			SET @SQl1=LEFT(@SQl1,LEN(@SQl1)-1)
			--PRINT @SQL1
			EXEC(@SQl1)
		END
	END
	ELSE --数据库小于50G
	BEGIN
		IF(CHARINDEX('Datacenter',@@VERSION)>0 OR CHARINDEX('Enterprise',@@VERSION)>0) --是否包含Datacenter 或者 Enterprise 版本
		BEGIN
			--压缩备份
			SET @SQL='BACKUP DATABASE '+@DB_DataBaseName+' TO DISK= N'''+@path+@DB_DataBaseName+'.bak'' with STATS = 1,compression';--压缩备份
			--PRINT @SQL
			EXEC(@SQL)
		END
		ELSE
		BEGIN
			--一般备份
			SET @SQL1='BACKUP DATABASE '+@DB_DataBaseName+' TO DISK='''+@path+@DB_DataBaseName+'.bak'''
			--PRINT @SQL1
			EXEC(@SQL1)
		END
	END
		
	SET @t = @t+1
	DROP TABLE  #database_size
END
DROP TABLE #TableName
END