Get default backup path of sql server programmatically

Question

I am taking backups of certain sql server databases programmatically using c#. I figured that Microsoft.SqlServer.Management.Smo and some other libraries are made for this purpose. Now I can backup a database. Very nice. Here is the code :

var server = new Server(@"" + InstanceName);
var backuper = new Backup();
try
{
    backuper.Action = BackupActionType.Database;
    backuper.Database = DbName;
    backuper.Devices.AddDevice(DbName + ".bak", DeviceType.File);
    backuper.BackupSetName = DbName + " - Yedek";
    backuper.BackupSetDescription = "Açık Bulut Depo - " + DbName + " - Yedek";
    backuper.ExpirationDate = DateTime.Now.AddYears(20);
    server.ConnectionContext.Connect();
    backuper.SqlBackup(server); 
}
catch(Exception ex){//..}

My question here is how can I get the path of the device that the database backed up into? I know I can specify my own path as :

backuper.Devices.AddDevice("C:\SOMEPATH\" + DbName + ".bak", DeviceType.File);

Then I can actually know where it is, but what I want to do is back it up to its default location and get its path. Please help me out with this.

Maybe once the backup is complete, you can find the most recent backup set associated with the database and work it out from there? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 11, 2013 at 12:55

Community · Accepted Answer · 2017-05-23 12:09:52Z

9

Correct Answer to this duplicate can be found here: https://stackoverflow.com/a/8791588/331889

Server.BackupDirectory;

Given you are already using SMO Objects it should be the simplest answer.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered May 7, 2014 at 22:59

Adam Hardy

4464 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

E.J. Brennan · Accepted Answer · 2013-07-11 11:35:21Z

6

From this blog post, you could use the function below:

http://www.mssqltips.com/sqlservertip/1966/function-to-return-default-sql-server-backup-folder/

CREATE FUNCTION dbo.fn_SQLServerBackupDir() 
RETURNS NVARCHAR(4000) 
 AS 
BEGIN 

 DECLARE @path NVARCHAR(4000) 

EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', 
        @path OUTPUT,  
        'no_output' 
RETURN @path 
END;

answered Jul 11, 2013 at 11:35

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

4 Comments

Tolga Evcimen Over a year ago

how can I call this from c#?

E.J. Brennan Over a year ago

It would need to be a separate db call than the SMO code above; you would call it the same way you would call any sql select statement: "SELECT dbo.fn_SQLServerBackupDir() as backuppath" and then read the results .

Tolga Evcimen Over a year ago

Thank you it worked like a charm :) my only concern now is that this software is supposed to be used on several databases by some end users who wants to create a backups of their databases, what if the user that they connect to database with does not have necessary privilages to create functions?

E.J. Brennan Over a year ago

You don't necessarily need the function, they do need permission however to execute master.dbo.xp_instance_regread which you could try to call another way. Not sure there is a workaround if they can't be granted that permission.

StackTrace · Accepted Answer · 2013-07-11 11:58:59Z

I normally execute the below stored procedure immediately after backuper.SqlBackup(server); to return the most recent backup destination path. I use this approach because using SMO, i give the application user the flexibility of backing up to any location / drive or even to a USB disk. So a user may decide not to backup to the default backup location and i want to return that location after the backup process is successfully completed.

  USE [YouDatabaseNameHere]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    -- =============================================
    -- Author:      SQL.NET Warrior
    -- =============================================
    CREATE PROCEDURE [dbo].[GetBackupHistory]
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @SQLVer SQL_VARIANT
            ,@DBName VARCHAR(128)
            ,@NumDays   SMALLINT
            ,@SQL       VARCHAR(1024)
            ,@WhereClause   VARCHAR(256)

    SET @DBName = Null
    ;
    SET @NumDays = 14
    ;
    SET @SQLVer = CONVERT(INTEGER, PARSENAME(CONVERT(VARCHAR(20),SERVERPROPERTY('ProductVersion')),4));

    SET @WhereClause = 'WHERE a.type IN (''D'',''I'')
            And a.backup_start_date > GETDATE()- ' + CAST(@NumDays AS VARCHAR)+''
    IF @DBName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + '
            AND a.database_name = '''+ @DBName +''''
    END

    SET @SQL = '
    SELECT TOP 1 a.database_name,a.backup_start_date
            ,b.physical_device_name AS BackupPath
            ,a.position
            ,a.type
            ,a.backup_size/1024/1024 AS BackupSizeMB
            ,' + CASE 
                WHEN @SQLVer < 10 
                    THEN '0'
                    ELSE 'a.compressed_backup_size/1024/1024'
                END + ' AS CompressedBackMB
        FROM msdb.dbo.backupset a
            INNER JOIN msdb.dbo.backupmediafamily b
                ON a.media_set_id = b.media_set_id
        ' + @WhereClause + '
        ORDER BY a.backup_start_date DESC;';
         --PRINT @SQL
     EXECUTE (@SQL);
    END;



    GO

Collectives™ on Stack Overflow

Get default backup path of sql server programmatically

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related