Вывести список баз в sql и как узнать когда был последний бекап + где лежит физически сама база

 Список баз

select * from [master].[sys].[databases]
sp_helpdb

Бэкап

 

/************************************************************
 * k.moskvichev © 
 * Time: 20.07.2015 10:53:59
 ************************************************************/

--DROP TABLE #temp
SELECT 
            @@Servername                AS ServerName
      ,d.Name                     AS DBName
      ,MAX(b.Backup_finish_date)  AS databecap
       -- ,bmf.Physical_Device_name   AS gdebackup
      ,m.physical_name            AS FILENAME
      ,d.recovery_model_desc      AS modelBuckap
       ,m.size           AS DMSize  
       --INTO #temp
FROM   
       sys.databases d
       LEFT OUTER JOIN msdb..backupset b
            ON  b.database_name = d.name
                AND b.[type] = 'D'
                AND b.server_name = @@servername
                    -- FULL OUTER JOIN msdb.dbo.backupmediafamily bmf
                    --   ON  bmf.media_set_id = b.media_set_id
                    --AND bmf.[device_type] = '7'
                    
       FULL OUTER JOIN sys.master_files m
            ON  d.database_id = m.database_id
                AND m.[data_space_id] = '1'
      
WHERE  
       /*datepart(YEAR,Backup_finish_date) BETWEEN 2015 AND 2015 and
       datepart(month,Backup_finish_date) BETWEEN 07 AND 07 and
       datepart(day,Backup_finish_date) BETWEEN 06 AND 09 and*/
       d.Name NOT IN ('master' ,'msdb' ,'msdb' ,'model' ,'tempdb')
       --  AND d.NAME = 'alcohol'
GROUP BY
       d.name
       -- ,bmf.Physical_Device_name
      ,m.physical_name
      ,d.recovery_model_desc
       --,b.Backup_finish_date
       ,m.size
ORDER BY
       SERVERname 
       --,Backup_finish_date DESC

Similar Posts:

Метки:

Добавить комментарий