MSSQL Backup Checks

I have a few scripts here that I got from my colleagues to check the recent backup on all database in your MSSQL server. This will help us monitor if we have recent backups available. Specially when the your backup job history is not available. 

The script below checks the recent backups on all the databases in your SQL server.
use master
  go
  select
@@servername as ServerName,
db.name, 
test.UserName,
test.BackupSize, 
test.Duration, 
test.BackupAge, 
test.FinishDate, 
test.BackupType,
test.DeviceType,
test.BackupLocation, 
GETDATE()
from sysdatabases db
left join (
  select cast(database_name AS varchar(30)) AS [DBName],
   cast(bs.user_name AS varchar(30)) AS [UserName],
 str(cast(bs.backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],
 cast(datediff(n,bs.backup_start_date,bs.backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],
 cast(datediff(dd,bs.backup_finish_date,Getdate()) AS varchar(10))  AS [BackupAge],
 convert(varchar(20),bs.backup_finish_date) AS [FinishDate],
 case when bs.type = 'D' then 'Full'
      when bs.type = 'I' then 'Diff'
      when bs.type = 'L' then 'Log'
      when bs.type = 'F' then 'FileGroup' end as [BackupType],
     case when mf.device_type = 2 then 'Disk'
      when mf.device_type = 5 then 'Tape'
      when mf.device_type = 6 then 'Pipe'
      when mf.device_type = 7 then 'Virtual'
      when mf.device_type in (102, 105, 106) then 'Permanent' end as [DeviceType],
 mf.physical_device_name as [BackupLocation]
  from
 msdb..BACKUPSET bs,
 msdb..backupmediafamily mf
  where 
 mf.media_set_id = bs.media_set_id
 and bs.backup_set_id in (
  select max(backup_set_id) 
  from msdb..BACKUPSET 
  where type = 'D'
  group by database_name)
  ) test
on test.DBName = db.name
where db.name <> 'tempdb'

The script below checks the backups for a specific database. You can take advantage of the WHERE clause where you can specify which specific Backup would you like to check per databases.

SELECT TOP 10

s.database_name,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

WHERE s.database_name = DB_NAME() and s.type='D'-- Remove this line for all the database and s.BackupType='D'

ORDER BY backup_start_date DESC, backup_finish_date

GO

Till my next blog entry. :)