Get default backup path of sql server programmatically

Related searches

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.

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;

Function to Return Default SQL Server Backup Folder, The author blogs about SQL Server topics at his personal blog: thesqlagentman. com. Last Updated: 2010-03-19. get scripts � next tip button� A simple function call to dbo.fn_SQLServerBackupDir() will now provide you with the default backup directory value that was defined through the facet for the SQL Server instance:

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.

Backup to the right location, by default., read the current SQL Server default backup location ***/ it read the local computer registry but with this we can get to any value in the registry. If we open the registry using REGEDIT or some other tool and if you navigate to this key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer or similar for your instance of SQL Server we can see the following information. The highlighted line below shows the default directory for the backups for this one instance.

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

T-sql to get the default backup path? – SQLServerCentral, For instance say I have 5-10 instances on the same server, I need to go and check the path for each and specify the @key parameter? Isn't it� The backup file gets placed into the default backup directory, which is in my case 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs.bak'. The only problem is that SQL server doesn't know

How do i find the default backup path for a sql server instance using , bak on remote sql server instances. I believe i can accomplish this by gettting the default backup path from the server and creating my backup� We can then retrieve the different Settings property to list the default SQL Server Backup folder $s.Settings | Get-Member -MemberType Property The BackupDirectory property gives us the default SQL Server Backup directory value. This property can be modified as well but for our purpose, retrieving it would be enough.

Change default database file and backup paths in SQL Server on , You can find all database file in this path, i.e. data files, log files and the backup files. Once we create any database, it goes to this default location� It needs to be changed using the Linux configuration utility of SQL Server, i.e. mssql-conf. In the above screenshot, you can see the default file location is ‘ /var/opt/mssql/data ‘. Let us go to the default path (/var/opt/mssql/data). Here we can view the content using the ‘ ls -lrt ‘ command.

Not very relevant any more, and I'm only posting it as a bit of trivia, but there is also a default path for full-text catalogs there. I wrote a script years ago to run against all of my sQL

Comments
  • Maybe once the backup is complete, you can find the most recent backup set associated with the database and work it out from there?
  • how can I call this from c#?
  • 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 .
  • 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?
  • 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.