jakz jakz - 7 months ago 11
SQL Question

T-sql for checking SQL Server EDITION can compress backup

How can you check, through t-sql, if your SQL-server edition (not version) supports compressing backups through

WITH COMPRESSION


?

Also, if only possible to do this through checking your edition somehow (like this: http://stackoverflow.com/a/1658724/1655580), how re-usable is such an approach if developing against SQL Server 2008 R2 and then comparing with later versions?

What I'm primarily interested in is if there is a 'direct' way to get to know this, and not indirectly via checking the edition.

Thanks

PS:

This

USE my_database;
GO
SELECT value
FROM sys.configurations
WHERE name = 'backup compression default' ;


is not what I'm looking for, but is the only thing I get when I searched myself.

Answer

You could try to use it and see whether it produces an error:

exec(N'set noexec on; backup database master
to disk=''c:\backup''
WITH COMPRESSION')
print @@ERROR

If it's valid, @@ERROR will be 0. If it's invalid, it will be 155. Of course, the NOEXEC means it doesn't actually attempt to run the backup operation. You can use a similar technique to discover the availability or not of any features that will produce parse time errors.