Backup compression behavior when appending backups to an existing Media Set
There will be an upcoming Knowledge base article detailing this out as well
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’ 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’ option and do not specify the backup statement level “WITH COMPRESSION” option
Backup Compression on SQL Server does have the restrictions listed below
https://msdn.microsoft.com/en-us/library/bb964719(SQL.100).aspx
One of the main restrictions is that Compressed and uncompressed backups cannot co-exist in a media set. This article tries to supplement this information specifically with regards to the backup compression option of sp_configure and it’s behavior in some edge cases.
When we create a new media set, we do encode whether it is for a compressed backup in the media header. Backups taken to an existing media set can co-exist only if the compression setting is the same as of the media set. There is a difference in behavior when we backup based on whether we use the default backup compression setting in sp_configure or the “WITH compression” or the “WITH no_compression” option of the backup statement when appending to a Media set. 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 the “With No_Compression” option of the 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.
The following is a table that tries to encapsulate the backup behavior when compression is at play.
| New MediaSet | Append to Media Set | ||
| Existing Media Set has Compressed Backup | Exsting Media Set has uncompressed backup | ||
| Backup statement level “WITH COMPRESSION” clause | Success Backup compressed | Success | Errors |
| Backup statement level “WITH NO_COMPRESSION” clause | Success Backup - uncompressed | Errors | Success |
| Backup statement without Compression clause | Success Compression depends on sp_configure ‘backup compression’ setting | Success Backup will be compressed | Success Backup will be uncompressed |
Here is a sample Script to demonstrate that behavior. The Behavior is the same whether the backup is to TAPE or a disk.
NOTE: Tape is not natively included in Windows Server 2008 R2.
-- Note compression value, by default it should be 0
sp_configure 'backup compression'
-- Initial Backup completes successfully
BACKUP DATABASE test TO DISK = N'E:\testbackup.bak' WITH FORMAT, INIT,
NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD, STATS = 10
GO
-- Check the backup and the header, not the Compressed value
restore 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 = 10
GO
Msg 3098, Level 16, State 2, Line 1
The 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 1
BACKUP DATABASE is terminating abnormally.
-- Turn on default backup Compression at the server level
sp_configure 'backup compression',1
go
reconfigure
go
-- 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 and does not fail
BACKUP DATABASE test TO DISK = N'E:\testbackup.bak' WITH
NAME = N'testbackup-Full Database Backup', SKIP,NOREWIND,NOUNLOAD
, STATS = 10
GO
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 have expected this to fail 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 media set using the FORMAT option, then the current compression setting is inherited
-- Create a new media set using FORMAT Or by specifying a new file
BACKUP DATABASE test TO DISK = N'E:\testbackup.bak' WITH FORMAT, INIT
, NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD, STATS = 10
GO
-- Check the backup and meadia set header
restore 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 0
sp_configure 'backup compression',0
go
reconfigure
go
-- backup to the same media set with INIT
BACKUP DATABASE test TO DISK = N'E:\testbackup.bak' WITH INIT
, NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD, STATS = 10
GO
-- 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 compressed
restore headeronly from DISK = N'E:\testbackup.bak'
Another limitation is that Compressed Backups cannot Co-exist with NT backups
-- Take an A backup to TAPE from either NT backup ( Windows 2003 ) or some other utility such that there exists a backup on the tape and the backup is taken outside of SQL Server ( not a SQL Backup)
-- You can verify the backup Header now, see it is not a SQL backup And also the Backup Name indicates that as well.
restore headeronly from DISK = N'E:\testbackup.bak'
-- Take a SQL backup to the same media set with INIT and compression and you get the error message
BACKUP DATABASE test TO TAPE = N'\\.\Tape0' WITH INIT,COMPRESSION
, NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD, STATS = 10
GO
Msg 3098, Level 16, State 2, Line 1
The 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 1
BACKUP DATABASE is terminating abnormally.
--Take a SQL backup to the same media set without initializing and NO compression and the backups ( NT and non-compressed backup) can co-exist
BACKUP DATABASE test TO TAPE = N'\\.\Tape0' WITH
NAME = N'testbackup-Full Database Backup', SKIP,NOUNLOAD, STATS = 10
GO
-- can verify the backup Header now,see the SQL and the NT backup
Restore headeronly from tape = N'\\.\Tape0'
-- Forcing a Compressed backup on a tape with an NT backup results in the error below
BACKUP DATABASE test TO TAPE = N'\\.\Tape0' with compression,
NAME = N'testbackup1 Full Database Backup', SKIP,NOUNLOAD, STATS = 10
GO
Msg 3098, Level 16, State 2, Line 1
The 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 1
BACKUP DATABASE is terminating abnormally.
-Denzil Ribeiro