Backups présents sur les disques de données

Cette requêtes identifie les bases de données dont les backups sont stockés sur les mêmes disques que les données.

/*-------------------------------------------------------------------
[SCRIPT] backups on data disks  
[DATABASE] msdb
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
SELECT dbf.name AS DatabaseName
     , bkf.name
     , UPPER(dbf.hdd) AS DatabaseFilesOnDisk
     , UPPER(bkf.hdd) AS BackupFilesOnDisk
FROM   (   SELECT   name
                  , LEFT(physical_name, 1) AS hdd
           FROM     sys.master_files
           GROUP BY name
                  , LEFT(physical_name, 1)) AS dbf
       JOIN (   SELECT   db.name
                       , db.create_date
                       , MAX(bk.backup_finish_date) lastbackup
                       , bk.type
                       , LEFT(MAX(bm.physical_device_name), 1) AS hdd
                FROM     master.sys.databases db
                         LEFT JOIN msdb..backupset bk ON db.name = bk.database_name
                         LEFT JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id
                WHERE    db.name <> 'tempdb'
                         AND bm.device_type = 2
                GROUP BY db.name
                       , db.create_date
                       , bk.type ) AS bkf ON dbf.name = bkf.name
WHERE  dbf.hdd = bkf.hdd;

/*-------------------------------------------------------------------

[SCRIPT] backups on data disks

[DATABASE] msdb

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

SELECT dbf.name AS DatabaseName

, bkf.name

, UPPER(dbf.hdd) AS DatabaseFilesOnDisk

, UPPER(bkf.hdd) AS BackupFilesOnDisk

FROM ( SELECT name

, LEFT(physical_name, 1) AS hdd

FROM sys.master_files

GROUP BY name

, LEFT(physical_name, 1)) AS dbf

JOIN ( SELECT db.name

, db.create_date

, MAX(bk.backup_finish_date) lastbackup

, bk.type

, LEFT(MAX(bm.physical_device_name), 1) AS hdd

FROM master.sys.databases db

LEFT JOIN msdb..backupset bk ON db.name = bk.database_name

LEFT JOIN msdb..backupmediafamily bm ON bk.media_set_id = bm.media_set_id

WHERE db.name <> 'tempdb'

AND bm.device_type = 2

GROUP BY db.name

, db.create_date

, bk.type ) AS bkf ON dbf.name = bkf.name

WHERE dbf.hdd = bkf.hdd;