How can I tell if a SQL Server backup is compressed?

We have recently upgraded from SQL Server 2005 to SQL Server 2012. Under SQL Server 2005 there is no option to create compressed backups as there is in 2012.

If you attempt BACKUP DATABASE ... WITH (COMPRESSION); to a file that has already been initialized without compression, the BACKUP DATABASE command will fail with the following error message:

ERROR MESSAGE : BACKUP DATABASE is terminating abnormally.
ERROR CODE : 3013

How can I tell if an existing backup file is initialized for compressed backups?

Maybe instead of backing up over and over again to the same file, you should consider using WITH INIT and a new file always. I think it is simpler to manage multiple backup files, all with their own timestamp embedded into the filename, and be able to archive/purge each file individually. When you keep dumping backups to the same file, it just gets bigger and bigger, and harder to manage IMHO. Never mind that you no longer have to care that

Also I am not sure why you would ever be turning backup compression on and off. Have you found a case where disabling it is better? Do you have real use cases where on an edition that supports compression you are taking one-off backups without compression, and using the same file? Why?

Anyway you can always do something very simple like:

BEGIN TRY
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak' WITH COMPRESSION, ...;
END TRY
BEGIN CATCH
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak', ...;
END CATCH

The error bubbles up immediately before any work is done.

But I still think it is much better to just not use the same file over and over again in the first place. IMHO.

StackExchange Database Administrators Q#37609, answer score: 7