May 15, 2015 at 1:42 pm
Comments posted to this topic are about the item Complete Details About Backups Taken a Day Ago
Thanks.
June 5, 2015 at 7:50 am
-- Modified allowing for SQL Server 2014 and fixed compatibility of 110 for SQL Server 2012
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
Declare @FromDate as datetime;
-- Specify the from date value
SET @FromDate = GETDATE() -1;
SELECT
RTRIM(ltrim(CONVERT(CHAR(100), SERVERPROPERTY('Servername')))) AS SQLServerName,
msdb.dbo.backupset.database_name,
msdb.dbo.backupfile.page_size, -- Size of the page, in bytes.
msdb.dbo.backupfile.physical_name,
msdb.dbo.backupfile.is_present, -- 1 = File is contained in the backup set.
--msdb.dbo.backupfile.file_type,
CASE msdb.dbo.backupfile.file_type
WHEN 'D' THEN 'Data File'
WHEN 'L' THEN 'Log File'
WHEN 'F' THEN 'Full text catalog'
ELSE msdb.dbo.backupfile.file_type
END AS file_type,
Convert(numeric(10,2),msdb.dbo.backupfile.file_size/1024/1024) 'File_Size_MB', -- Length of the file that is backed up, in bytes. Can be NULL.
msdb.dbo.backupfile.backed_up_page_count,
msdb.dbo.backupfile.source_file_block_size, -- Device that the original data or log file resided on when it was backed up. Can be NULL.
msdb.dbo.backupfile.logical_name,
msdb.dbo.backupfile.physical_name,
CASE msdb.dbo.backupset.compatibility_level
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END AS CompatibilityLevel,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
END AS backup_type,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
--msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
(msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
--msdb.dbo.backupset.backup_size/1024 'Actual backup_size_KB',
msdb.dbo.backupset.backup_size/1024/1024 'Actual backup_size_MB',
msdb.dbo.backupset.is_password_protected 'Is_Backup_Password_Protected',
msdb.dbo.backupset.is_damaged 'Is_Backup_Damaged', -- 1 = Damage to the database was detected when this backup was created. The backup operation was requested to continue despite errors.
CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
--CASE WHEN ISNULL([compressed_backup_size], 0) > 0 THEN [compressed_backup_size] / [backup_size] END '[Compression Ratio]',
Convert(INT,COALESCE([compressed_backup_size]/1024, msdb.dbo.backupset.backup_size/1024) / NULLIF(DATEDIFF(second, [backup_start_date], [backup_finish_date]),0)) '[Thruput ( kilo-bytes per second )]' ,
--CAST(msdb.dbo.backupset.backup_size / (DATEDIFF(minute,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date) + 0.0000001)/1024.0/1024.0 AS DECIMAL(16,2)) AS mb_per_minute,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database - full'
WHEN 'L' THEN 'Database - Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
ELSE msdb..backupset.type
END AS backup_type,
msdb.dbo.backupmediafamily.logical_device_name,
--msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupmediafamily.physical_device_name AS PhysicalDevice,-- backup Physical location
CASE device_type
WHEN 2 THEN 'Disk - Temporary'
WHEN 102 THEN 'Disk - Permanent'
WHEN 5 THEN 'Tape - Temporary'
WHEN 7 THEN 'Virtual device'
WHEN 105 THEN 'Tape - Permanent backup device'
--ELSE 'Other Device'
ELSE CAST('Unknown: ' + device_type AS NVARCHAR(15))
END AS DeviceType,-- Device type
msdb.dbo.backupset.description,
msdb.dbo.backupmediaset.is_compressed
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
INNER JOIN msdb.dbo.backupmediaset ON msdb.dbo.backupmediaset.media_set_id = msdb.dbo.backupmediafamily.media_set_id
INNER JOIN msdb.dbo.backupfile ON msdb.dbo.backupfile.backup_set_id = msdb.dbo.backupset.backup_set_id
WHERE
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date;
SET NOCOUNT OFF;
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
June 5, 2015 at 8:08 am
I wrote it keeping in view of sql 2012, as I dont have 2014.
However, thnx for modifying it for sql 2014. Nice job.
Thanks.
June 8, 2015 at 7:30 am
Thanks for the script. I'm not involved in our backups but they tell me we have them taken every half hour.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply