Pages

Thursday, November 28, 2013

T-SQL to get Database backup details

Below is a simple yet powerful query to get the details about your database backup. This query can be customised as per your needs as this is simple join to two tables in MSDB Database.


use msdb
go
select bus.name,bus.[user_name],bus.backup_finish_date,
((((bus.backup_size)/1024)/1024)/1024) as 'backup_size in GB',bus.database_name,
CASE
WHEN bus.[type] = 'D' Then 'Full Backup'
WHEN bus.[type] = 'I' Then 'Differential Database Backup'
WHEN bus.[type] = 'L' Then 'Log Backup'
WHEN bus.[type] = 'F' Then 'File or filegroup Backup'
WHEN bus.[type] = 'G' Then 'Differential file Backup'
WHEN bus.[type] = 'P' Then 'Partial Backup'
WHEN bus.[type] = 'Q' Then 'Differential partial Backup'
End AS 'backup_Type',
bumf.physical_device_name,
CASE
WHEN bumf.device_type = '2' Then 'Disk'
WHEN bumf.device_type = '5' Then 'Tape'
WHEN bumf.device_type = '7' Then 'Virtual device'
WHEN bumf.device_type = '105' Then 'permanent backup device'
End AS 'Backup_Device_Type'
from backupset bus
left outer Join backupmediafamily bumf
on bus.media_set_id = bumf.media_set_id
where bus.database_name = 'YourDatabaseName'
order by bus.backup_finish_date desc


No comments:

Post a Comment