Список баз
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: