T-SQL to find backup location | SansSQL

Monday, May 26, 2014

T-SQL to find backup location

Here is a T-SQL query to find the location of the backup files.

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

No comments:

Post a Comment