Today, We try to check of SQL server when trying to take Compress & UnCcomprees Backups in one Media\File.
Query No. 1
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL.BAK’ WITH COMPRESSION
GO
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL.BAK’ WITH NOINIT
Result : Run Successfully
Explanation : Backup file is formatted to take compressed backup. So without mention COMPRESSION keyword. SQL server process this backup request as COMPRESSED backup.
Query No. 2
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL2.BAK’ WITH COMPRESSION
GO
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL2.BAK’ WITH INIT
Result : Run Successfully
Explanation : Backup set is overwritten but file header is intact. Due to which without mention COMPRESSION keyword, SQL server process this backup request as COMPRESSED backup.
Query No. 3
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL3.BAK’
GO
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL3.BAK’ WITH INIT,COMPRESSION
Result : Failed
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.
Explanation : Backup file is formatted to take Uncompressed backup but backup is mention to take in compressed form.
Query No. 4
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL4.BAK’
GO
BACKUP DATABASE DB2
TO DISK = ‘D:\DB2_FULL4.BAK’ WITH NOINIT,COMPRESSION
Result : Failed
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.
Explanation : Backup set is overwritten but file header is intact. Due to COMPRESSION keyword, SQL server tried to process this backup request as COMPRESSED backup in Uncompressed file format & got failed.
