You are currently offline, waiting for your internet to reconnect

Behavior of compressed backups when appending backups to an existing media set

SUMMARY
One of the main restrictions with compressed backups is that compressed and uncompressed backups cannot co-exist in a media set. This restriction is documented in the following topic in Books Online:


This article supplements that documentation and provides more information on the expected behavior of compressed backups in relation to backup compression default server configuration setting.
MORE INFORMATION
When you append a compressed backup to an existing media, it inherits the compression setting from the media set. If you rely on the backup compression's sp_configure setting and are appending to existing media sets you may end up with a backup in a different compression state than expected
This is true only under the following circumstances:
  • You Append the backup to an existing media set.
  • You rely on the sp_configure ‘backup compression default’ option and do not specify the backup statement level “WITH COMPRESSION” option.

When a media set is created, information on whether this media set is for a compressed backup or a normal backup is written to te media header file. 
Backups taken to an existing mediaset can co-exist only if the compression setting of these backups is the same as that of the media set. The following three factors affect the behavior of compressed backups.
  1. SQL Server's configuration option - backup compression default
  2. Backup Set Options - COMPRESSION or NO_COMPRESSION
  3. Whether you are appending to an existing media set or writing the backup to a new media set. For existing media, an additional factor to consider is whether the media set currently contains a compressed or an uncompressed backup.

The following table summarize the behavior of compressed backups based on the above 3 factors.





New MediaSetAppend to Media Set
Backup Statement OptionsExisting Media Set has Compressed BackupExsting Media Set has uncompressed backup
Backup  statement level “WITH COMPRESSION clauseSuccess

Backup compressed
SuccessError
Backup  statement level “WITH NO_COMPRESSION clauseSuccess
Backup - uncompressed
ErrorSuccess
Backup statement without statement level compression
clause
Success
Compression depends on sp_configure ‘backup compression’ setting
Success

Backup will be compressed
Success

Backup will be uncompressed


As you can see from the above table, when we use the default compression setting at the server and append to an existing media set, the backup will never fail due to a mismatch in compression settings. It works but inherits the setting in the header of the media set. However if you specify the WITH COMPRESSION or WITH NO_COMPRESSION options in your backup statement, an error will be raised if there is a mismatch between the backup stored in the media set and the current backup being taken in terms of the compression setting.


Note: You can find the current setting for backup compression default option by running sp_configure command in the SQL Server Management Studio. If you are appending to existing media you can get the header information using restore headeronly command. For further information refer to the Examples section later in this article.

Examples: Here is a sample script to demonstrate the behavior for various cases. The behavior is the same whether the backup is to a tape or a disk.

-- Note compression value, by default it should be 0sp_configure 'backup compression'-- Initial Backup completes successfullyBACKUP DATABASE test TO  DISK = N'E:\testbackup.bak' WITH FORMAT, INIT,  NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD,  STATS = 10GO-- Check the backup and the header, and see the Compressed column value, it is 0restore headeronly from DISK = N'E:\testbackup.bak'-- Now backup using "with compression" and it will fail as backups ( compressed and non compressed cannot be mixed within the same media set )BACKUP DATABASE test TO  DISK = N'E:\testbackup.bak' WITH NAME = N'testbackup-Full Database Backup', SKIP,NOREWIND,NOUNLOAD,COMPRESSION , STATS = 10GOMsg 3098, Level 16, State 2, Line 1The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally. -- Turn on default backup Compression at the server levelsp_configure 'backup compression',1 'backup compression',1goreconfigurego -- The sp_configure ‘default compression’ as this point is set to 1.-- Given that you may expect the backup to be compressed and it will be if it -- is a new media set-- However if you backup and append to the same media set, the backup works -- but results in an uncompressed backup BACKUP DATABASE test TO  DISK = N'E:\testbackup.bak' WITH NAME = N'testbackup-Full Database Backup', SKIP,NOREWIND,NOUNLOAD, STATS = 10GO Processed 2 pages for database 'test', file 'test_log' on file 2.BACKUP DATABASE successfully processed 162 pages in 6.211 seconds (0.203 MB/sec). -- Check the backup and meadia set header.You will see that though Server default is set to compressed, the backup given that it is appended to an existing media set inherits the compression setting of the media set itself-- You may expect this to have failed with the same error as when specifying the WITH COMPRESSION clause in the backup statement given that compressed and non compressed backups cannot co-exist in the media set. restore headeronly from DISK = N'E:\testbackup.bak' --If you create a new mediaset using the FORMAT option, then the current compression setting is inherited-- Create a new media set using FORMAT Or by specifying a new fileBACKUP DATABASE test TO  DISK = N'E:\testbackup.bak' WITH FORMAT, INIT,  NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD,  STATS = 10GO-- Check the backup and meadia set headerrestore headeronly from DISK = N'E:\testbackup.bak' -- If you use the with INIT,  the backup sets are overwritten but the media header is not-- Toggle the backup compression setting back to 0sp_configure 'backup compression',0goreconfigurego-- backup to the same media set with INITBACKUP DATABASE test TO  DISK = N'E:\testbackup.bak' WITH  INIT,  NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD,  STATS = 10GO-- Check the backup and meadia set header-- Note that even though we changed backup compression to 0, the old media header is preserved which has it as 1, and the backup goes as compressedrestore headeronly from DISK = N'E:\testbackup.bak'Another limitation is that Compressed Backups cannot Co-exist with NT backups:-- Take an NT Backup-- You can verify the backup Header now, see it is not a SQL backuprestore headeronly from DISK = N'E:\testbackup.bak'-- backup to the same media set with INIT and compression and you get the error messageBACKUP DATABASE test TO  TAPE = N'\\.\Tape0' WITH  INIT,COMPRESSION,  NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD,  STATS = 10GOMsg 3098, Level 16, State 2, Line 1The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.-- backup to the same media set without initializing and NO compression and the backups ( NT and non-compressed backup) can co-existBACKUP DATABASE test TO  TAPE = N'\\.\Tape0' WITH  NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD,  STATS = 10GO-- You can verify the backup Header now,see the SQL and the NT backupRestore headeronly from tape = N'\\.\Tape0'-- Forcing a Compressed backup on a tape with an NT backup results in the error belowBACKUP DATABASE test TO  TAPE = N'\\.\Tape0' with compression, NAME = N'testbackup1 Full Database Backup', SKIP,NOUNLOAD,  STATS = 10GOMsg 3098, Level 16, State 2, Line 1The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.



Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 2297053 - Last Review: 10/04/2010 17:56:00 - Revision: 2.0

Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise

  • KB2297053
Feedback
om/c.gif?DI=4050&did=1&t=">