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.

Since I won't know what version of SQL will be installed on the machine where the application runs, I need to be able 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 won't work, since this assumes that only one instance of SQL is installed.

Another expample, created a database, and then read that database's file properties, before deleting the database again. 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

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. This solution also makes it easier to rename the database in the process if, for example, you want to tack a date onto the name.

Answer 2

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:

select filename from master.dbo.sysdatabases where name = 'master'

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.

Answer 3

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\\"