T-SQL Function to Get the Default Backup Folder

Again, DBA’s are often faced with adhoc backup tasks that shall dump relevant data or log to the existing backup location for all other data/log dumps. Quick way to do so will invovling concatenating dynamic sql commands with the default backup folder location.
[sourcecode language=”sql”]
/*

Script to create a function to get default backup directory of SQL Server.

SQL Version: 2005+

Last modified by Nick Xu on 2014-08-01

–Usage
SELECT dbo.fn_GetBackupDir()
go
*/

–For generic setting
USE master
GO

IF OBJECT_ID(‘dbo.fn_GetBackupDir’) IS NOT NULL
DROP FUNCTION dbo.fn_GetBackupDir
GO

CREATE FUNCTION dbo.fn_GetBackupDir()
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;
[/sourcecode]
And to take one step further, the below script prints the commands your need to backup all non-system DB’s in the default backup folder with the current timestamp:
[sourcecode language=”sql”]
SELECT ‘BACKUP DATABASE [‘ + name + ‘] TO DISK = ”’ +
DBADB.dbo.fn_GetBackupDir() + ‘\’ + name + ‘_’ + DBADB.dbo.fn_GetTimeStamp() + ‘.bak WITH COMPRESSION, STATS=1, CHECKSUM”’
FROM sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘tempdb’, ‘msdb’)
[/sourcecode]
And even one more step futher is the below script that deletes backups that are older than N days from the default bacup folder, courtersy to Patrick Keisler’s good work:
[sourcecode language=”sql”]
DECLARE @Path AS VARCHAR(500)

SELECT @Path = DBADB.dbo.fn_GetBackupDir()

DECLARE @DeleteDate DATETIME = DATEADD(DAY,-3,GETDATE());

EXEC master.sys.xp_delete_file 0,@Path, ‘BAK’,@DeleteDate,0;
[/sourcecode]