1 개요

2 기본값 설정

3 압축백업 예제

4 압축률

5 백업 튜닝

6 참고 자료

SQL Server 2008에서는 압축백업을 지원한다. SQL Server 2008 Enterprise Edition에서만 지원되는 기능으로 최초 설치 시에는 비활성화 되어 이 기능을 사용하려면 관리자가 활성화시켜줘야 한다. 압축백업과 일반백업의 성능 테스트 방법(Perfmon을 이용한 비교)은 다음의 성능카운터를 이용하여 비교하면 된다.

• Device Throughput Bytes/sec, SQLServer:Backup Device
  
• Backup/Restore Throughput/sec, SQLServer:Databases
  
  

use master
go

exec sys.sp_configure n'backup compression default', n'1'
go

reconfigure with override
go

USE master;
GO
EXEC sp_configure 'backup compression default', '0';
RECONFIGURE WITH OVERRIDE;
GO

USE AdventureWorks2008;
GO
SELECT A.*
INTO temp
FROM HumanResources.Employee a, HumanResources.Employee b, HumanResources.Employee c
GO

USE master
GO
BACKUP DATABASE AdventureWorks2008
TO DISK='C:\AdventureWorks2008.bak' 
WITH FORMAT
GO
--BACKUP DATABASE이(가) 571823개의 페이지를 174.033초 동안 처리했습니다(25.669MB/초).
--백업본 약 4GB

BACKUP DATABASE AdventureWorks2008
TO DISK='C:\AdventureWorks2008_Compression.bak' 
WITH FORMAT, COMPRESSION
GO
--BACKUP DATABASE이(가) 571821개의 페이지를 66.747초 동안 처리했습니다(66.929MB/초).
--백업본 약 100MB

SELECT 
	database_name 
,	backup_size/1024/1024 [backup_size(MB)]
,	compressed_backup_size/1024/1024 [compressed_backup_size(MB)]
,	CONVERT(varchar, CONVERT(int, backup_size/compressed_backup_size) * 100) + '%' AS [Compression_Ratio(%)]
,	DATEDIFF(ss, backup_start_date, backup_finish_date) [ProcessTime(sec)]
,	database_creation_date 
FROM msdb..backupset
ORDER BY 
	database_creation_date DESC
,	backup_set_id DESC;

또는

SELECT 
  convert(date, b.backup_finish_date) backup_finish_date,
  b.database_name 'Database Name', 
  CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)', 
  CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)', 
  CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / 
  CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio', 
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)' 
FROM 
  msdb.dbo.backupset b 
WHERE 
  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 
  AND b.backup_size > 0 
ORDER BY 
  b.backup_finish_date DESC

다음의 아티클을 참고하면 된다. 아주 잘 설명해 놓았다. 영어 못하는 주인놈도 그림과 소스로 이해가 충분히 간다.
Tuning the Performance of Backup Compression in SQL Server 2008(http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx)

백업 성능은 다음과 같은 요소에 가장 큰 영향을 받는다.

• 백업 디스크 수
  
• CPU
  
• MAXTRANSFERSIZE 옵션
  
• BUFFERCOUNT 옵션
  

• Backup compression in SQL Server 2008(http://sqlblog.com/blogs/tibor_karaszi/archive/2007/12/12/backup-compression-in-sql-server-2008.aspx)
  
• Backup More Than 1GB per Second Using SQL2008 Backup Compression(http://blogs.msdn.com/sqlcat/archive/2008/03/02/backup-more-than-1gb-per-second-using-sql2008-backup-compression.aspx)
  
• http://blog.it-hero.co.kr/blog_post_41.aspx
  
• SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times)(http://www.sqlservercentral.com/articles/Compression/62746/)
  
  

마지막 링크를 보면 3.26GB를 압축백업했더니 백업본의 크기가 940MB가 되었으며, 압축백업을 하지 않았을 때는 백업 시간이 6분 걸렸으며, 압축백업을 했을 때는 3분이 걸렸다고 한다.

net use z: \\192.168.1.2\Backup p@ssword /user:backup_user /persistent:yes
copy c:\pubs.bak z:\
del /Q c:\pubs.bak
net use /del z:

현재 주인놈의 회사에서는 써드파티 제품으로 압축백업을 받고 있다. DW라 그냥 백업 받기가 좀 부담스러운 상태다. MVP인 최석준님(같은 회사 다닌다)께서 살짝 테스트 해봤는데 써드파티 제품은 SQL Server에서 자원이 필요하면 백업하다가 살짝 돌려주는데 2008의 압축 백업은 그냥 막 백업한다고 한다. 돈주고 사는 것이 좋기는 좋은가부다. 물론 불편한 점도 있다. 2000버전에서 2005로 업그레이드 할 때 이노무 써드파티 제품때문에 확장프로시저 찾아서 다시 다 만들어주느라고 상당히 귀찮았던 기억이다.