Getting the sql server default backup folder on Linux with T-SQL

For SQL Server on Windows for getting default backup folder we can use master.dbo.xp_instance_regread:

    DECLARE @HkeyLocal nvarchar(18) = N'HKEY_LOCAL_MACHINE';
    DECLARE @MSSqlServerRegPath nvarchar(31) = N'SOFTWAREMicrosoftMSSQLServer';
    DECLARE @InstanceRegPath sysname = @MSSqlServerRegPath + N'MSSQLServer';

    DECLARE @BackupDirectory nvarchar(512)
    if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0)
    SET @BackupDirectory=cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512))
    else
    EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT;

    SELECT @BackupDirectory AS SQLServerBackupDirectory;

But for Linux it does not work. Can anyone help with T-SQL approach (I only find SSMS solution here)?

Answer

I’ve never found a non-registry way to get the backup directory reliably in t-sql. I usually just do a replace of /data/ to /backup/ since at default install it’s relation to the data directory is clear. This does not directly answer your question but it’s a working way provided you have not done individual directory customization during install.

-- for one of my databases, [dharma] in my linux test-bed.
declare @bkup nvarchar(1024)
set @bkup = replace(cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512)),'/data/','/backup/') + 'dharma.bak'
backup database dharma to disk = @bkup