Technical FAQs

Ask a Question

How to Find the Physical SQL Backup File Location

Issue
Locating the physical file location, backup type, and the date the backup was performed can be a tedious and time consuming task
when unfamiliar with the system and its maintenance procedures.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Cause
Database restoration, maintenance and/or troubleshooting possible application and database issues.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Execute the SQL query below to find the backup file location for all SQL database backups performed.  A "Where database_name = <yourdatabase>'' clause
could be added just prior to the Order By clause to narrow the results to only the database of interest.

SELECT database_name AS DBName
    ,physical_device_name AS BackupLocation
    ,CASE
        WHEN [TYPE]='D' THEN 'FULL' 
        WHEN [TYPE]='I' THEN 'DIFFERENTIAL'
        WHEN [TYPE]='L' THEN 'LOG'
        WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
        WHEN [TYPE]='G'  THEN 'DIFFERENTIAL FILE'
        WHEN [TYPE]='P' THEN 'PARTIAL'
        WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
     END AS BackupType
    ,backup_finish_date AS BackupFinishDate
FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily
ON(backupset.media_set_id=backupmediafamily.media_set_id)
ORDER BY backup_finish_date DESC


** see attachment containing the SQL script text

Was this helpful?
What can we do to improve the information ?