Earlier a few weeks ago, I ran into an interesting error message.
Msg 3098, Level 16, State 2, Line 10 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 10 BACKUP LOG is terminating abnormally.
Recreate the Error
These are the steps I performed, when I ran into this error.
Step 1: Perform database backup using BACKUP LOG command without the clause “WITH COMPRESSION”
-- -- Perform LOG backup (without COMPRESSION) -- BACKUP LOG Ahold_RT TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\Without_Compression.TRN' GO
Step 2: Perform backup again (using same file and location), but this time use “WITH COMPRESSION”
-- -- Now perform backup to the same file and location <strong>WITH COMPRESSION</strong> -- BACKUP LOG Ahold_RT TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\Without_Compression.TRN' WITH COMPRESSION GO
Result: Error message
- When Sql Server performs backup, it stores the backup file in a certain format.
- When we perform multiple backups using the same file, the new backup data is appended to the original backup file.
Given these, it is important the the first backup and second backup going to the same file and location, need to be in the same format.
- Either perform backup to a different location. Or
- Perform backup using “WITH FORMAT” clause to invalidate the previous file and then create a fresh new one with new format.
-- -- Perform backup using 'WITH FORMAT' -- BACKUP LOG Ahold_RT TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\Without_Compression.TRN' WITH COMPRESSION , FORMAT GO