備份壓縮 (SQL Server)Backup Compression (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題會說明 SQL ServerSQL Server 備份的壓縮,包括限制、壓縮備份的效能取捨、備份壓縮的組態及壓縮比率。This topic describes the compression of SQL ServerSQL Server backups, including restrictions, performance trade-off of compressing backups, the configuration of backup compression, and the compression ratio. 下列 SQL Server 2017SQL Server 2017 版本支援備份壓縮:Enterprise、Standard 和 Developer。Backup compression is supported on SQL Server 2017SQL Server 2017 editions: Enterprise, Standard, and Developer. 每個 SQL Server 2008SQL Server 2008 版本與更新版本都可以還原壓縮的備份。Every edition of SQL Server 2008SQL Server 2008 and later can restore a compressed backup.

優點Benefits

  • 由於壓縮的備份小於相同資料的未壓縮備份,所以壓縮備份通常需要更少的裝置 I/O 而且通常會大幅提升備份速度。Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

    如需詳細資訊,請參閱本主題稍後介紹的< 壓縮備份的效能影響>。For more information, see Performance Impact of Compressing Backups, later in this topic.

限制Restrictions

下列限制適用於壓縮的備份:The following restrictions apply to compressed backups:

  • 壓縮和未壓縮的備份無法在媒體集中並存。Compressed and uncompressed backups cannot co-exist in a media set.

  • 舊版 SQL ServerSQL Server 無法讀取壓縮的備份。Previous versions of SQL ServerSQL Server cannot read compressed backups.

  • NTbackup 無法與壓縮的 SQL ServerSQL Server 備份共用磁帶。NTbackups cannot share a tape with compressed SQL ServerSQL Server backups.

壓縮備份的效能影響Performance Impact of Compressing Backups

根據預設,壓縮會大幅增加 CPU 使用量,而且壓縮程序所耗用的額外 CPU 可能會對並行作業造成不良的影響。By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. 因此,您可能會想要在其 CPU 使用量由資源管理員限制的工作階段中,建立低優先權的壓縮備份。Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited byResource Governor. 如需詳細資訊,請參閱本主題稍後介紹的< 使用資源管理員進行備份壓縮,以限制 CPU 使用率 (Transact-SQL)限制的工作階段中,建立低優先權的壓縮備份。For more information, see Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

若要全盤了解備份 I/O 效能,您可以透過評估下列效能計數器種類,隔離往返裝置之間的備份 I/O:To obtain a good picture of your backup I/O performance, you can isolate the backup I/O to or from devices by evaluating the following sorts of performance counters:

如需有關 Windows 計數器的詳細資訊,請參閱 Windows 說明。For information about Windows counters, see Windows help. 如需如何使用 SQL Server 計數器的相關資訊,請參閱 使用 SQL Server 物件For information about how to work with SQL Server counters, see Use SQL Server Objects.

計算壓縮備份的壓縮率Calculate the Compression Ratio of a Compressed Backup

若要計算備份的壓縮率,請使用 backupset 記錄資料表的 backup_sizecompressed_backup_size 資料行中的備份值,如下所示:To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_sizecompressed_backup_sizebackup_size:compressed_backup_size

例如,壓縮比 3:1 表示您節省了大約 66% 的磁碟空間。For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space. 若要針對這些資料行進行查詢,您可以使用下列 Transact-SQL 陳述式:To query on these columns, you can use the following Transact-SQL statement:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;  

壓縮備份的壓縮比取決於已經壓縮的資料。The compression ratio of a compressed backup depends on the data that has been compressed. 有許多因素可能會影響取得的壓縮比。A variety of factors can impact the compression ratio obtained. 主要的因素包括:Major factors include:

  • 資料的類型。The type of data.

    字元資料的壓縮比較其他資料類型要高。Character data compresses more than other types of data.

  • 頁面上資料列之間的資料一致性。The consistency of the data among rows on a page.

    一般而言,如果某個頁面包含許多資料列,而且其中某個欄位包含相同的值,則系統可能會針對該值進行大幅壓縮。Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. 反之,如果某個資料庫包含隨機資料或者每個頁面僅包含單一大型資料列,則壓縮的備份幾乎會與未壓縮的備份一樣大。In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.

  • 資料是否經過加密。Whether the data is encrypted.

    加密資料的壓縮比大幅低於對等的未加密資料。Encrypted data compresses significantly less than equivalent unencrypted data. 如果使用透明資料加密來加密整個資料庫,則壓縮備份可能不會大幅縮減其大小 (如果有的話)。If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.

  • 資料庫是否經過壓縮。Whether the database is compressed.

    如果資料庫已壓縮,壓縮備份可能不會大幅縮減其大小 (如果有的話)。If the database is compressed, compressing backups might not reduce their size by much, if at all.

配置備份檔案的空間Allocation of Space for the Backup File

對於壓縮備份,最後備份檔案的大小取決於資料可壓縮的程度,但是在備份作業完成之前,無法得知這項資訊。For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. 因此,根據預設,使用壓縮備份資料庫時,Database Engine 會針對備份檔案使用預先配置的演算法。Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. 此演算法預先為備份檔案配置了預先定義的資料庫大小百分比。This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. 如果在備份作業期間需要更多空間,Database Engine 會增加檔案大小。If more space is needed during the backup operation, the Database Engine grows the file. 如果最後的大小小於配置的空間,在備份作業結束時,Database Engine 會將檔案縮小為最後的實際備份大小。If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

若要讓備份檔案只依照需要增加至最終大小,請使用追蹤旗標 3042。To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. 追蹤旗標 3042 會導致備份作業略過預設備份壓縮預先配置演算法。Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. 如果您只要配置壓縮備份所需的實際大小,藉以節省空間,這個追蹤旗標就很有用。This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. 不過,使用此追蹤旗標可能會導致效能稍微降低 (可能會增加備份作業的持續時間)。However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

相關工作Related Tasks

另請參閱See Also

備份概觀 (SQL Server) Backup Overview (SQL Server)
追蹤旗標 (Transact-SQL)Trace Flags (Transact-SQL)