Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Function to Return Default SQL Server Backup Folder


By:   |   Read Comments (5)   |   Related Tips: More > Backup

Attend a SQL Server Conference for FREE >> click to learn more


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'

 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 (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();

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 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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, March 04, 2014 - 10:32:38 AM - Jason Back To Top

Why not use a table in a DBA database.


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

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

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

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

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 
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\Setup',
	N'SQLDataRoot',
@SQLDataRoot OUTPUT
-- Datenverzeichnis
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultData',
@DefaultData OUTPUT
-- Logverzeichnis
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultLog',
@DefaultLog OUTPUT
-- Backupverzeichnis
EXEC master.dbo.xp_instance_regread
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'BackupDirectory',
@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,

tosc


Learn more about SQL Server tools