C# SQL Restore database to default data location

Question

I'm writing a C# application which downloads a compressed database backup via FTP. The application then needs to extract the backup and restore it to the default database location.

I will not know which version of SQL Server will be installed on the machine where the application runs. Therefore, I need to find the default location based on the instance name (which is in the config file).

The examples I found all had a registry key which they read, but this will not work, since this assumes that only one instance of SQL is installed.

Another example I found created a database, read that database's file properties, the deleting the database once it was done. That's just cumbersome.

I did find something in the .NET framework which should work, ie:

Microsoft.SqlServer.Management.Smo.Server(ServerName).Settings.DefaultFile

The problem is that this is returning empty strings, which does not help.

I also need to find out the NT account under which the SQL service is running, so that I can grant read access to that user on the backup file once I have the it extracted.

Answer 1

What I discovered is that

Microsoft.SqlServer.Management.Smo.Server(ServerName).Settings.DefaultFile

only returns non-null when there is no path explicitly defined. As soon as you specify a path which is not the default, then this function returns that path correctly.

So, a simple workaround was to check whether this function returns a string, or null. If it returns a string, then use that, but if it's null, use

Microsoft.SqlServer.Management.Smo.Server(ServerName).Information.RootDirectory + "\\DATA\\"