Contents

1
2 蠍磯蓋螳 れ
3 豢覦煙
4 豢襯
5 覦煙
6 谿瑚 襭


1 #

SQL Server 2008 豢覦煙 讌. SQL Server 2008 Enterprise Edition襷 讌 蠍磯レ朱 豕豐 れ 觜燕 蠍磯レ る 蟯襴螳 燕貅譴 . 豢覦煙螻 朱覦煙 焔 ろ 覦覯(Perfmon 伎 觜蟲) れ 焔レ拘危磯ゼ 伎 觜蟲覃 .
  • Device Throughput Bytes/sec, SQLServer:Backup Device
  • Backup/Restore Throughput/sec, SQLServer:Databases

2 蠍磯蓋螳 れ #

use master
go

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

reconfigure with override
go

3 豢覦煙 #

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

4 豢襯 #

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

5 覦煙 #

れ 壱伎 谿瑚覃 . 譯 る . 覈詩 譯殊碁 蠏碁手骸 る 危願 豢覿 螳.
[http]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 旧

6 谿瑚 襭 #

襷讌襷 襷襯 覲企 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襦 蠏碁企 企碁 覓語 ロ襦 谿場 れ 襷れ伎朱手 麹 蠏谿 蠍一旧企.