adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Вывести список баз в 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>