Function to Return Default SQL Server Backup Folder

By:   |   Comments (7)   |   Related: > Backup


I have a number of scripts I run against new SQL Server installs to, among other things, create my SQL Server Agent backup jobs.  Typically, the jobs for my backup processes are all the same, across my environment with the exception of a few variables.  One of those is the default backup folder where I plan to store my backup files locally prior to moving them from disk to tape for long term, offsite storage.  In the past I've had to do one of two things when it came to replacing the references to the backup directory in my code:

  • Manually enter the values into the code where applicable.
  • Use templates for the code and replace the references to the backup path with parameters I could replace prior to running the script.

Either of these solutions work.  However, the point of using these scripts is to reduce the time to stage a server as much as possible.  Looking up the default backup path and revising the code each time the script is run, even via template parameters is still a pain.  However, there is a way to identify this SQL Server instance setting and avoid having to alter the script each time it's run simply by creating a user defined function to return this setting.


Some may balk when I mention that we're going to query a registry setting to return this information, but it is exactly what we're going to do.  The value for the backup path is located in the registry under HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory.  Returning this information is possible with using the xp_instance_regread extended stored procedure as follows:

EXEC  master.dbo.xp_instance_regread  
 N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory' 
create my SQL Server Agent backup jobs

This matches with the information you would return through examining the facets or the properties for the SQL instance via the SQL Server Management Studio GUI.

examining the facets or the properties for the SQL instance via the SQL Server Management Studio GUI.

Now to construct the function that will allow us to use this information in my job creation scripts (and any other scripts you or I want to create that may require this information).  It is simply the matter of creating a user-defined function around the xp_instance_regread extended stored procedure:

--Author: Timothy Ford ([email protected])  
-- returns nvarchar(4000) = dbo.fn_SQLServerBackupDir() 
IF OBJECT_ID('dbo.fn_SQLServerBackupDir') IS NOT NULL 
   DROP FUNCTION dbo.fn_SQLServerBackupDir 

CREATE FUNCTION dbo.fn_SQLServerBackupDir() 

   DECLARE @path NVARCHAR(4000) 

   EXEC master.dbo.xp_instance_regread 
            @path OUTPUT,  
   RETURN @path 


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:

SELECT fn_SQLServerBackupDir = dbo.fn_SQLServerBackupDir(); 
A simple function call to dbo.fn_SQLServerBackupDir() will now provide you with the default backup directory value

Just how do I use this information?  Well, when I backup my databases I do so through calling a stored procedure that is set up to backup specific groups of databases to a defined backup device, when dealing with full backups, or to a specific directory when calling my transaction log backup stored procedure.  These stored procedure calls occur within a SQL Server Agent job that is scheduled on each of my instances.  I log the results of the job runs to log files that are stored in subfolders under my backup directory so that I can review any issues or outcomes from a backup processing cycle.  I've mentioned how to set up SQL Server Agent logging before in one of my first tips for  I'll be sharing these backup processes in future tips so I ask that you register with in order to be alerted when those future companion articles are posted. 

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, January 10, 2022 - 6:57:25 PM - Jeff Moden Back To Top (89643)
@Hiram Fleitas,

You're working entirely too hard...

EXEC master.dbo.xp_instance_regread

Monday, April 29, 2019 - 1:41:22 AM - Hiram Fleitas Back To Top (79825)

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

Tuesday, March 4, 2014 - 10:32:38 AM - Jason Back To Top (29637)

Why not use a table in a DBA database.

Wednesday, August 24, 2011 - 10:43:40 AM - Filipe Back To Top (14504)

I stand corrected. It DOES WORK even for named instances, and it does not matter what version of SQL (2000,2005, or 2008).

Not only it works, but if you try to access the right registry path it won't work. Very interesting.

Wednesday, August 24, 2011 - 10:28:29 AM - Greg Robidoux Back To Top (14503)

Surprisingly it does work. 

I ran this on a default instance and a named instance and got the following results for the script in the tip.  I did not run the script above from TOSC.

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup

Wednesday, August 24, 2011 - 9:53:54 AM - Filipe Back To Top (14499)

This might be all well and dandy for default instances of SQL, but it won't work (as is) for named instances :-)

When we have named instances this values go under...

SQL 2005:

HLKM\Software\Microsoft\Microsoft SQL Server\MSSQL.x (where x is instance number on this server)

SQL 2008:

HLKM\Software\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>

Not sure what would happen if you had mixed instances in one server.

Friday, March 19, 2010 - 8:03:07 AM - tosc Back To Top (5083)

Hi Tim,

and this script returns the defaults of Root -, Data - , Log - and Backup - Folder:

-- =============================================
-- Defaults
-- =============================================

DECLARE @SQLDataRoot nvarchar(512)
DECLARE @DefaultData nvarchar(512)
DECLARE @DefaultLog nvarchar(512)
DECLARE @BackupDirectory nvarchar(512)

-- Defaults überprüfen
-- Basisverzeichnisse auslesen

-- Installationsverzeichnis
EXEC master.dbo.xp_instance_regread 
-- Datenverzeichnis
EXEC master.dbo.xp_instance_regread
@DefaultData OUTPUT
-- Logverzeichnis
EXEC master.dbo.xp_instance_regread
@DefaultLog OUTPUT
-- Backupverzeichnis
EXEC master.dbo.xp_instance_regread
@BackupDirectory OUTPUT

IF LEFT(REVERSE(@SQLDataRoot), 1) <> N'\'
SET @SQLDataRoot = @SQLDataRoot + N'\'
    -- Regschlüssel = 0 dann Standardverzeichnis 
SET @DefaultData =  ISNULL(@DefaultData, @SQLDataRoot + 'DATA')
SET @DefaultLog =  ISNULL(@DefaultLog, @SQLDataRoot + 'DATA')

I wish you a nice day,


get free sql tips
agree to terms