How can I obtain the default backup path for SQL S

Question

I have a web application that I'll be distributing to customers. I'll give them the option of backing up and restoring the back-end SQL Server database through an admin web page.

For backup I just programmatically create a file name using time-stamps and let SQL Server save it in the default backup folder. For restoring, I'd like to list the backup files and let the user choose which one to use, but I don't know what the default backup folder is to get a file listing.

How can I programmatically obtain the default backup folder for SQL Server 2008 Express R2 using C#?

Thanks

Answer 1

Try to adopt this stub:

    private void GetSqlDefaultInfo(string InstanceName, string ServerName)
    {
        try
        {
            InstanceName = string.IsNullOrEmpty(InstanceName) ? "MSSQLSERVER" : InstanceName;

            if (string.IsNullOrEmpty(ServerName))
                ServerName = Environment.MachineName;
            using (var registryKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, ServerName))
            {
                object sqlInstance;
                using (var subKey = registryKey.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"))
                    sqlInstance = subKey.GetValue(InstanceName);
                if (sqlInstance != null && !string.IsNullOrEmpty(sqlInstance.ToString()))
                {
                    var sqlPathKey = string.Format(@"SOFTWARE\Microsoft\Microsoft SQL Server\{0}\MSSQLServer",
                                                   sqlInstance);
                    object defaultData, defaultLog, backupDirectory, sqlPath;
                    using (var subKey = registryKey.OpenSubKey(sqlPathKey))
                    {
                        defaultData = subKey.GetValue("DefaultData");
                        defaultLog = subKey.GetValue("DefaultLog");
                        backupDirectory = subKey.GetValue("BackupDirectory");
                    }
                    sqlPathKey = string.Format(@"SOFTWARE\Microsoft\Microsoft SQL Server\{0}\Setup", sqlInstance);

                    using (var subKey = registryKey.OpenSubKey(sqlPathKey))
                        sqlPath = subKey.GetValue("SQLDataRoot");
                    DataFilePath = defaultData != null
                                       ? defaultData.ToString()
                                       : Path.Combine(sqlPath.ToString(), "Data").TrimEnd('\\');

                    LogFilePath = defaultLog != null
                                      ? defaultLog.ToString()
                                      : Path.Combine(sqlPath.ToString(), "Data").TrimEnd('\\');
                    FTSIndexFilePath = DataFilePath;
                    ContentFilePath = DataFilePath;
                    BackupFilePath = backupDirectory != null
                                         ? backupDirectory.ToString()
                                         : Path.Combine(sqlPath.ToString(), "Backup").TrimEnd('\\');
                }
            }
        } catch(Exception)
        {

        }
    }

Answer 2

The following query should give you the physical device name (or path) for each database. You can tailor it to suit your needs:

select
    database_name,
    backup_type,
    physical_device_name -- path
from
(
    select 
        row_number() over (partition by database_name,type order by backup_start_date desc) as rownum,
        database_name,
        case type 
            when 'L' then 'Log' 
            when 'D' then 'Data'
            else '??? '+type 
        end as backup_type,
        physical_device_name
    from msdb.dbo.backupset a 
    join msdb..backupmediaset b on a.media_set_id = b.media_set_id 
    join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id
) x
where rownum=1
order by database_name asc, backup_type

Answer 3

To retrieve the Backup path for a given server you could use the SQL Server Management Objects. The Server object has a property called BackupDirectory.

You'll want something like this:

Server srv = new Server("SERVERNAME");
string backUpDir = srv.BackupDirectory;

For this to work you will need to import usings/references for:

Microsoft.SqlServer.Management.Smo;
Microsoft.SqlServer.Management.Common;

You will find more information here on how to interact with the SQL Server Management Objects.

Answer 4

I ended up using the following SqlCommand ... This solution appeared in another answer that was for some reason erased.? Anyway, here is the command I ended up using:

EXEC  master.dbo.xp_instance_regread  N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer',N'BackupDirectory'

And here is the command in my C# code which shows the default backup folder path getting stored in the variable backupFolder:

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);

            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = "EXEC  master.dbo.xp_instance_regread  N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer',N'BackupDirectory'";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = myConnection;
            myConnection.Open();

            SqlDataReader myDataReader = cmd.ExecuteReader();

            myDataReader.Read();
            string backupFolder = myDataReader.GetString(1);

Answer 5

The value you're looking for is stored in the registry

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer
Value: BackupDirectory

Note that the key depends on your SQL Server instance name.

Answer 6

declare @regread nvarchar(max)

select  top 1 @regread = substring(registry_key,6,len(registry_key)-16)
from    sys.dm_server_registry
where   registry_key like '%Parameters'
group by registry_key

select @regread = 'exec master..xp_regread ''HKEY_LOCAL_MACHINE'','''+@regread+''', ''BackupDirectory'''
exec sp_executesql @regread