Browse FAQs

How to Find the Physical SQL Backup File Location

Published date: 18 September 2019

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, SQL Server 2016, SQL Server 2017

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. To narrow the results to only the database of interest, un comment the "Where clause" and change the database name --Where database_name Like 'ION_Data'

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)
--Where database_name Like 'ION_Data'
ORDER BY backup_finish_date DESC

** see attachment containing the SQL script text

BackupLocate.sql

Can't find what you are looking for?

Reach out to our customer care team to receive information on technical support, assistance for complaints and more.

Contact support