Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Backup Paths and File Management


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

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

So the queries about backups are stored in my "Backup" folder and my "Restore" folder.
Next Steps


Last Update:


signup button

next tip button



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

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, August 13, 2013 - 8:33:01 PM - Graham Okely Back To Top

Mohsen

Thank you for your comments. Yes I have seen numbers like those before and I have found they mean an automated backup by Backup Exec, Netbackup or somethng liek those has run and the numbers are from that system. So you may find a backup has been taken by some tape device. Check with your SAN administraor, backup contact or system administrator. Those numbers indicate a backup has been taken but probably not to a local drive on your SQL Server.

All the best.

Graham Okely


Sunday, August 11, 2013 - 3:57:57 AM - Mohsen (HS) Back To Top

Dear Graham

It was very useful for me. I really appreciate this tutorial.

There is only one problem when I exec the codes. As I run the code as an outpu,t SQL gives me this as Phisical_device_name:

{2F32CB7D-52FA-449A-8B44-E5FB6193C1A6}1

can you give me some tips to solve this problem and it shows me regular drive name like drive c or d ,..

I am using sql erver 2012 in win 8

Any help is appreciated.

 

Thanks

Mohsen

 

 


Learn more about SQL Server tools