Query To Find Backup Location In SQL Server

We use simple T-SQL commands to retrieve the PATH from our msdb backup records. T-SQL doesn’t seem to have a method to slice up a folder/file path and return the parent folders so we have to use CHRAINDEX, SUBSTRING and REVERSE to pull out the file name leaving just the path.

Here is the query grabbing the backup path for the master DB,.. and YES it does look like a mess.

SELECT 
REVERSE(
  SUBSTRING(
     REVERSE(bmf.physical_device_name), 		  
          CHARINDEX('\', REVERSE(bmf.physical_device_name)
	), LEN(bmf.physical_device_name)))
FROM msdb.dbo.[backupset] bs
	INNER JOIN msdb.dbo.[backupmediafamily] bmf
	ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'master'
ORDER BY backup_finish_date DESC

The “physical_device_name” field in the MSDB database stores the location of our last backup. The path I am dealing with is actually not a local folder as some servers may be using, we store the backups on network storage and use a UNC or shared path that looks something like this:

\\backupserver\folder1\sql_instance\database\backup_filename.bak

The idea is to return the folder for a specific backup or a list of folders to be used by another process and this can be modified for a local folder too. The trick is that the MSDB record includes the backup file name and we want a script that does not need to be changed if the backup location changes.

Powershell is my usual GO TO for something like this but I thought I would share how we did this in SQL first. There are a few other methods and I’m sure this can be optimized too.

REVERSE

Looking at the statement we need to start on the “inside” of the string of commands to make sense, this is the REVERSE command.

We need to remove (or save) the filename in order to leave the folder path remaining. We REVERSE the string so we can use the CHARINDEX to find the “first” occurrence of the slash and therefore the end of the filename. Try this to see the results:

SELECT REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak')

You end up with a reversed string:

kab.emanelif_pukcab\esabatad\ecnatsni_lqs\1redlof\revrespukcab\

CHARINDEX

With the string reversed, using CHARINDEX we can now find the location of a certain character in this case it’s the forward slash (‘/’).

SELECT CHARINDEX('\', REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak'))

Index = 20

We now know that the filename is exactly 20 characters long and we can use this to remove it from the end of the string. This number needs to be used by SUBSTRING to tell us where to stop when returning the string characters.

SUBSTRING

At this point we have we can plug in the string and number of characters in order to pull out the path BUT the string is currently (db tales com) reversed at this point. We use the REVERSE string, the character index (20) and the LENgth of the string to tell SUBSTRING where to start and stop.

SELECT SUBSTRING(
	REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak'),  
	CHARINDEX('\',REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak')),
	LEN('\\backupserver\folder1\sql_instance\database\backup_filename.bak'))

If you run this command you will see that the filename is gone and it leaves the trailing slash. To remove this slash you can add a +1 to the CHARINDEX method. In our case we kept the slash.

CHARINDEX('\',REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak'))+1,

FINAL

Since the string is still reversed the last step is to REVERSE it again so we have the original path without the filename anymore.

SELECT REVERSE(
	SUBSTRING(
	     REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak'),  
	     CHARINDEX('\',REVERSE('\\backupserver\folder1\sql_instance\database\backup_filename.bak')),
	     LEN('\\backupserver\folder1\sql_instance\database\backup_filename.bak')))