SQLTeam.com | Weblogs | Forums

Script-Backup Details


#1

Hello Experts,
I have written a script which displays the backup details for all the db's on the server.
This displays accumulative info showing the size of all backup files, size of FULL alone, size of DIFF alone and size of LOGs alone
I think there should be a better way of writing this.
can you please take a look at the below script.

select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Source_Server,
'All_FULL' as DatabaseName,
null as backup_start_date,
null as backup_finish_date,
'ALL' as BackuType,
SUM(bkSize_MB) as BackupSize_MB,
SUM(CompressedbkSize_MB) as Compressed_BackupSize_MB,
sum(datediff(SECOND,backup_start_date,backup_finish_date)) as Duration_Sec,
null as DatabaseCreationDate
from
(
SELECT
A.[Server],
A.DATABASE_NAME,
A.last_db_backup_date,

       B.expiration_date, 
       CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
	 CAST(b.compressed_backup_size / 1000000 AS INT) AS CompressedbkSize_MB,
	 B.backup_start_date,B.backup_finish_date,
       B.logical_device_name,  
       B.physical_device_name,   
       B.backupset_name, 
       B.description 
    FROM 
       ( 
       SELECT   
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
           msdb.dbo.backupset.database_name,  
           msdb.dbo.backupset.backup_finish_date AS last_db_backup_date 

       FROM    msdb.dbo.backupmediafamily  
           INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
       WHERE   msdb..backupset.type = 'D' 
       --GROUP BY 
       --    msdb.dbo.backupset.database_name  
       ) AS A 

       LEFT JOIN  

       ( 
       SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       msdb.dbo.backupset.backup_start_date,  
       msdb.dbo.backupset.backup_finish_date, 
       msdb.dbo.backupset.expiration_date, 
       msdb.dbo.backupset.backup_size,  
	 msdb.dbo.backupset.compressed_backup_size,
       msdb.dbo.backupmediafamily.logical_device_name,  
       msdb.dbo.backupmediafamily.physical_device_name,   
       msdb.dbo.backupset.name AS backupset_name, 
       msdb.dbo.backupset.description 
    FROM   msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
    WHERE  msdb..backupset.type = 'D' 
       ) AS B 
       ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 

) subquery
union
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Source_Server,
'All_DIFF' as DatabaseName,
null as backup_start_date,
null as backup_finish_date,
'ALL' as BackuType,
SUM(bkSize_MB) as BackupSize_MB,
SUM(CompressedbkSize_MB) as Compressed_BackupSize_MB,
sum(datediff(SECOND,backup_start_date,backup_finish_date)) as Duration_Sec,
null as DatabaseCreationDate
from
(
SELECT
A.[Server],
A.DATABASE_NAME,
A.last_db_backup_date,

       B.expiration_date, 
       CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
	 CAST(b.compressed_backup_size / 1000000 AS INT) AS CompressedbkSize_MB,
	 B.backup_start_date,B.backup_finish_date,
       B.logical_device_name,  
       B.physical_device_name,   
       B.backupset_name, 
       B.description 
    FROM 
       ( 
       SELECT   
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
           msdb.dbo.backupset.database_name,  
           msdb.dbo.backupset.backup_finish_date AS last_db_backup_date 

       FROM    msdb.dbo.backupmediafamily  
           INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
       WHERE   msdb..backupset.type = 'I' 
       --GROUP BY 
       --    msdb.dbo.backupset.database_name  
       ) AS A 

       LEFT JOIN  

       ( 
       SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       msdb.dbo.backupset.backup_start_date,  
       msdb.dbo.backupset.backup_finish_date, 
       msdb.dbo.backupset.expiration_date, 
       msdb.dbo.backupset.backup_size,  
	 msdb.dbo.backupset.compressed_backup_size,
       msdb.dbo.backupmediafamily.logical_device_name,  
       msdb.dbo.backupmediafamily.physical_device_name,   
       msdb.dbo.backupset.name AS backupset_name, 
       msdb.dbo.backupset.description 
    FROM   msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
    WHERE  msdb..backupset.type = 'I' 
       ) AS B 
       ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 

) subquery
union
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Source_Server,
'All_Log' as DatabaseName,
null as backup_start_date,
null as backup_finish_date,
'ALL' as BackuType,
SUM(bkSize_MB) as BackupSize_MB,
SUM(CompressedbkSize_MB) as Compressed_BackupSize_MB,
sum(datediff(SECOND,backup_start_date,backup_finish_date)) as Duration_Sec,
null as DatabaseCreationDate
from
(
SELECT
A.[Server],
A.DATABASE_NAME,
A.last_db_backup_date,

       B.expiration_date, 
       CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
	 CAST(b.compressed_backup_size / 1000000 AS INT) AS CompressedbkSize_MB,
	 B.backup_start_date,B.backup_finish_date,
       B.logical_device_name,  
       B.physical_device_name,   
       B.backupset_name, 
       B.description 
    FROM 
       ( 
       SELECT   
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
           msdb.dbo.backupset.database_name,  
           msdb.dbo.backupset.backup_finish_date AS last_db_backup_date 

       FROM    msdb.dbo.backupmediafamily  
           INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
       WHERE   msdb..backupset.type = 'L' 
       --GROUP BY 
       --    msdb.dbo.backupset.database_name  
       ) AS A 

       LEFT JOIN  

       ( 
       SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       msdb.dbo.backupset.backup_start_date,  
       msdb.dbo.backupset.backup_finish_date, 
       msdb.dbo.backupset.expiration_date, 
       msdb.dbo.backupset.backup_size,  
	 msdb.dbo.backupset.compressed_backup_size,
       msdb.dbo.backupmediafamily.logical_device_name,  
       msdb.dbo.backupmediafamily.physical_device_name,   
       msdb.dbo.backupset.name AS backupset_name, 
       msdb.dbo.backupset.description 
    FROM   msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
    WHERE  msdb..backupset.type = 'L' 
       ) AS B 
       ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 

) subquery
union
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Source_Server,
'FULL+DIFF+LOG' as DatabaseName,
null as backup_start_date,
null as backup_finish_date,
'ALL' as BackuType,
SUM(bkSize_MB) as BackupSize_MB,
SUM(CompressedbkSize_MB) as Compressed_BackupSize_MB,
sum(datediff(SECOND,backup_start_date,backup_finish_date)) as Duration_Sec,
null as DatabaseCreationDate
from
(
SELECT
A.[Server],
A.DATABASE_NAME,
A.last_db_backup_date,

       B.expiration_date, 
       CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
	 CAST(b.compressed_backup_size / 1000000 AS INT) AS CompressedbkSize_MB,
	 B.backup_start_date,B.backup_finish_date,
       B.logical_device_name,  
       B.physical_device_name,   
       B.backupset_name, 
       B.description 
    FROM 
       ( 
       SELECT   
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
           msdb.dbo.backupset.database_name,  
           msdb.dbo.backupset.backup_finish_date AS last_db_backup_date 

       FROM    msdb.dbo.backupmediafamily  
           INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
       WHERE   msdb..backupset.type in('D','I', 'L') 
       --GROUP BY 
       --    msdb.dbo.backupset.database_name  
       ) AS A 

       LEFT JOIN  

       ( 
       SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       msdb.dbo.backupset.backup_start_date,  
       msdb.dbo.backupset.backup_finish_date, 
       msdb.dbo.backupset.expiration_date, 
       msdb.dbo.backupset.backup_size,  
	 msdb.dbo.backupset.compressed_backup_size,
       msdb.dbo.backupmediafamily.logical_device_name,  
       msdb.dbo.backupmediafamily.physical_device_name,   
       msdb.dbo.backupset.name AS backupset_name, 
       msdb.dbo.backupset.description 
    FROM   msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
    WHERE  msdb..backupset.type in('D','I', 'L')
       ) AS B 
       ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 

) subquery

union
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Source_Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupset.compressed_backup_size,
datediff(SECOND,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date) as Duration_Sec,
msdb.dbo.backupset.database_creation_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id


#2

your query is pretty hard to read. Can you please just post some examples of the output you want to see?


#3

no temp tables to be user..:smile:


#4

@Kristen format sql :grinning: