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.