<p>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.</p>

<p>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).</p>

<p>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.</p>

<p>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.</p>

<p>I did find something in the .NET framework which should work, ie:</p>

<p><pre><code>Microsoft.SqlServer.Management.Smo.Server(ServerName).Settings.DefaultFile</code></pre></p>

<p>The problem is that this is returning empty strings, which does not help.</p>

<p>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.</p>

[{'answer_id': 63200, 'author': 'Darrel Miller', 'author_id': 6819, 'author_profile': 'https://Stackoverflow.com/users/6819', 'pm_score': 1, 'selected': False, 'text': '<p>One option, that may be a simpler solution, is to create a new database on your destination server and then RESTORE over that database with your backup.  Your backup will be in the right place and you will not have to fuss with "MOVING" the backup file when you restore it.  SQL expects backups to be restored to exactly the same physical path that they were backed up from.  If that is not the case you have to use the MOVE option during RESTORE.\nThis solution also makes it easier to rename the database in the process if, for example,  you want to tack a date onto the name.</p>\n'}, {'answer_id': 63273, 'author': 'Chris Miller', 'author_id': 206, 'author_profile': 'https://Stackoverflow.com/users/206', 'pm_score': 2, 'selected': False, 'text': "<p>One way would be to use the same location as the master database.  You can query the SQL Server instance for that with the following SQL:</p>\n\n<pre><code>select filename from master.dbo.sysdatabases where name = 'master'\n</code></pre>\n\n<p>That will return the full path of the master database.  With that path, you can use the FileInfo object to extract the just the directory portion of that path.  That avoids the guess work of checking the registry for the instance of SQL Server that you are trying to connect to.</p>\n"}, {'answer_id': 119735, 'author': 'Richard C', 'author_id': 6389, 'author_profile': 'https://Stackoverflow.com/users/6389', 'pm_score': 4, 'selected': True, 'text': '<p>What I discovered is that </p>\n\n<pre><code>Microsoft.SqlServer.Management.Smo.Server(ServerName).Settings.DefaultFile\n</code></pre>\n\n<p>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.</p>\n\n<p>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</p>\n\n<pre><code>Microsoft.SqlServer.Management.Smo.Server(ServerName).Information.RootDirectory + "\\\\DATA\\\\"\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/63008', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6389/']