SQL Server Backup Paths and File Management

By: Graham Okely | Updated: 2013-06-05 | Comments (4) | Related: > Backup

Problem

How often do you execute a SQL Server backup on a remote server and then say to yourself, "Oh I need to copy that backup to another server" then go try to find it? Or how often do you want to clean out some old backup files off a SQL Server drive, but not sure which backups should be removed? Check out this tip to learn more about identifying and managing your backups on disk.

Solution

To answer the first question, we can use two system tables in the MSDB database. The first system table is called msdb.dbo.backupset and it has a row for each backup executed. The second system table is msdb.dbo.backupmediafamily and this table has a row for each media family. Let's build a query that reveals where the backups are located to learn more about identifying the backup location.

Query for Locating SQL Server Backups

-- File name : Where are the backups.sql
-- Author : Graham Okely B App Sc
-- Scope : OK on SQL Server 2000,2005,2008R2,2012
-- Select the information we require to make a decision about which backup we want to use

select  top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
 ,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'master%'
order by a.backup_finish_date desc

You can adjust the where clause in the query above to display your database backup. Note that a full list of database backup types is discussed here.

The screen shot below displays the top 5 results. It lists the following columns:

Server name
Database name
Backup finish date
Backup type
Backup path

the server_name against which we ran our TSQL

Building a better query to analyze SQL Server Backups

The image above shows the answer to our initial question, however let's try to gather more useful information from the query below:

-- File name : Where is my specific backup located.sql
-- Author : Graham Okely B App Sc
-- Scope : OK on SQL Server 2000,2005,2008R2,2012
-- Select the information we require to make a decision about which backup we want to use

select  top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
-- Build a path to the backup
,'\\' + 
-- lets extract the server name out of the recorded server and instance name
CASE
 WHEN patindex('%\%',a.server_name) = 0  THEN a.server_name
 ELSE substring(a.server_name,1,patindex('%\%',a.server_name)-1)
END 
-- then get the drive and path and file information
+ '\' + replace(b.physical_device_name,':','$') AS '\\Server\Drive\backup_path\backup_file'
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'master%'
order by a.backup_finish_date desc

Now we can see more helpful data. We can use the full path and database backup name when we use SQL Server Management Studio to recover a database or when we restore via T-SQL code. If you use SQL Server Management Studio, to restore a database you can paste the path and database backup name into the GUI when you are asked to supply the location of the backup.

use the full path and database backup name when we use the GUI to recover a database

A query to generate the location of the SQL Server Backup files

However let's say we want to copy that file or delete it after we copied the file from production to our test environment. How do we do that? Well let's modify our query.

-- File name : A query to a pathway.sql
-- Author : Graham Okely B App Sc
-- Select the information we require to make a decision about which backup we want to use

select  top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
-- Browse to the file
,'\\' + 
-- lets extract the server name out of the recorded server and instance name
CASE
 WHEN patindex('%\%',a.server_name) = 0  THEN a.server_name
 ELSE substring(a.server_name,1,patindex('%\%',a.server_name)-1)
END 
-- then get the drive information
+ '\' + left(replace(b.physical_device_name,':','$'),2) AS '\\Server\Drive'
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'master%'
order by a.backup_finish_date desc

On the very right you can see the server name and drive listed.

To use the information from this query to copy or delete a file follow these instructions:

In SQL Server Management Studio, click in the results field for the data you wish to browse to.
Alternate click and copy the data from the \\Server\Drive column.
Click on Start then Run on your workstation or current server.
- Windows key + R will do the same thing.
Then paste the data from the \\Server\Drive column into the run command.
Press enter and then, given you have permission, Windows Explorer will open up that drive.
Then browse down the folders to move or remove the backup files.
- You will know the folder from the results of the previous queries.
If you wish you can search for *.bak (or *.trn, etc.) using the Windows Explorer search box. See the image below and note the search box in the top right hand corner.

$Then paste into the run command area the copied cell result. (Example is: \\Server_name\G$)$

At this point you should be able to click on a backup file and press delete to clean up and create space. However be careful...usually your maintenance plans should clear up those old backups. So ask yourself "Why is it there?" first. Sometimes on a test environment or development environment you may find stray backups taking up space. They are the ones you should target as potential clean up items.

SQL Server Query for all Available Drives on a Server

Since we have been talking about backup storage, here is a handy query that will show all the drives on an instance. The xp_fixeddrives command will list the available drives on an instance. Most DBAs know that, all I have done is added some formatting that produces a useful path for each drive. I like to see the free space in gigabytes so I added that formatting.

-- File name : Space the final frontier.sql
-- Author : Graham Okely B App Sc
-- Purpose : Create the path for each drive on a SQL Server instance
-- Scope : OK on SQL Server 2000,2005,2008R2,2012

USE [Master]
GO

-- 2000 specific drop temp table
IF Object_id('tempdb..#Drives') IS NOT NULL
  DROP TABLE #Drives

-- Make a space for data
CREATE TABLE #Drives ( Drive_Letter  CHAR(1), mb_Free_Space int )

-- Collect the data
INSERT INTO #Drives EXEC xp_FixedDrives

-- Display a path to the drives
SELECT '\\' + CAST(Serverproperty('MachineName') AS NVARCHAR(128)) + 
 '\' + Drive_Letter + '$' AS 'Server and Drive'
       ,mb_Free_Space/1024 AS 'GB Free space'
FROM   #Drives

-- Clean up
DROP TABLE #Drives

Example results

Next Steps

Could you include one of these reports into an email to let a user know where their backup was located?
SQL Server 2012 has a nice wizard to copy databases. However it does not always work across versions of SQL Server.
If you are new see this restoration tutorial for command line restores.

General advice from Microsoft about copying databases with backup and restore.
Check out tip 2444 by Jugal Shah "Script to Get Available and Free Disk Space for SQL Server".
Check out tip 1263 by Jeremy Kadlec "Accessing the Windows File System from SQL Server".
Here is a link to a stored procedure version of determining free disk space. Tip 1706 by Jeremy Kadlec

About the author

Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2013-06-05