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
