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.
