Function to Return Default SQL Server Backup Folder

Problem

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.

Solution

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' 

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.

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 (sqlagentman@yahoo.com)  
---------http://thesqlagentman.com 
-- returns nvarchar(4000) = dbo.fn_SQLServerBackupDir() 
--****************************************************-- 
IF OBJECT_ID('dbo.fn_SQLServerBackupDir') IS NOT NULL 
   DROP FUNCTION dbo.fn_SQLServerBackupDir 
GO 
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;

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:

--TEST IT OUT: 
SELECT fn_SQLServerBackupDir = dbo.fn_SQLServerBackupDir(); 

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 MSSQLTips.com.  I’ll be sharing these backup processes in future tips so I ask that you register with MSSQLTips.com in order to be alerted when those future companion articles are posted. 

Next Steps

• Learn how to add verbose logging to your SQL Server Agent jobs from one of Tim’s other articles.

Tim Ford

Tim Ford is a Senior Database Administrator with MindBody in San Luis Obispo, California and is in the process of relocating west to the Pacific Northwest from Michigan. Since 2010 he’s produced Microsoft Data Platform training events branded as SQL Cruise from Alaska to the Caribbean and the Mediterranean at Tech Outbound, an events company specializing in technical training in unconventional locations. His SQL Cruise events take place on cruise ships in the Caribbean, Alaska, and the Mediterranean. Tim also is the Executive VP of Marketing for PASS, the global association for Microsoft data professionals. He also is a contributing author for itprotoday. Tim loves helping people find their true potential through education and building networks between Thought Leaders in various fields and those who are just starting on their careers or struggling to find their footing in established careers. If you’re looking for this sort of experience then check out the next SQL Cruise event taking place this August in Seattle.

• MSSQLTips Awards: Acheiver (75+ tips) – 2010