Find out in C# if my SQL Server instance supports snapshots?

Question

I want to write c# code to make a snapshot-backup if available. This is supported since SQL Server version 2005 but only in Enterprise edition but not in Express or Standard edition.

My question: how to find out in C# if the connected server supports snapshot backup (is Enterprise Edition 2005 or newer or some kind of "hasFeature(...))?

My current solution puts a try catch around the following code.

sqlCommand.CommandText = String.Format("CREATE DATABASE {0} ON " +
    (NAME = {1}, FILENAME = \"{2}\" ) AS SNAPSHOT OF {1}",
        databaseBackupName,
        databaseName,
        filenameOfDatabseBackup);
sqlCommand.ExecuteNonQuery();

If I catch a SqlException I assume there is no support on the connected server.

But maybe there could be other reasons to fail although the database supports snapshots (i.e. something is locked, connection is broken, ...)

The ideal solution would be some sqlCommand.ExecuteNonQuery() to find out if the feature is supported.

The second best is if I had to include some extra dll that can find it (?sqldmo?) but this would create an additional dependency to the project.

The third best would be some kind of exception handling.

Answer 1

You could certainly do something like this:

SELECT  
   SERVERPROPERTY('productversion') as 'Product Version', 
   SERVERPROPERTY('engineedition') as 'Engine Edition'

The Product Version will give you a string like this: 10.50.1600.1. SQL Server 2005 is version 9, so anything that starts with 9., 09. or 10., 11. will be fine.

The Engine Edition gives you an INT (1 = personal/MSDE/Express, 2 = standard, 3 = enterprise/developer).