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.

/*

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;

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:

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

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:

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

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server, T-SQL Script, User Defined Function and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s