T-SQL Snippets

jobs-categories.sql

-- Retrieve job names and categories 
SELECT	C.name AS category_name, J.name AS job_name
FROM	dbo.sysjobs AS J
JOIN	dbo.syscategories AS C
  ON	J.category_id = C.category_id
ORDER BY
	C.name;

last-backups-no-vss.sql

--
-- Retrieve last 3 backupsets per type per database filtering VSS snapshots
--
WITH BackupSets AS (
      SELECT database_name
      ,            CASE [type]
                   WHEN 'D' THEN 'Full'
                   WHEN 'I' THEN 'Differential'
                   WHEN 'L' THEN 'Log'
                   WHEN 'F' THEN 'File/Filegroup'
                   END backup_type
      ,            backup_start_date, backup_finish_date, physical_device_name
      ,            ROW_NUMBER() OVER (PARTITION BY database_name, [type] ORDER BY backup_start_date DESC) as rn
      FROM  msdb.dbo.backupset AS B1
      JOIN  msdb.dbo.backupmediaset AS B2
        ON  B1.media_set_id = B2.media_set_id
      JOIN  msdb.dbo.backupmediafamily AS B3
        ON  B1.media_set_id = B3.media_set_id
      WHERE physical_device_name NOT LIKE '{%'
)
SELECT database_name, backup_type
,            backup_start_date, backup_finish_date
,            CAST(backup_finish_date - backup_start_date AS time(0)) AS elapsed_time
,            physical_device_name
FROM  BackupSets
WHERE rn < 4
ORDER BY database_name, backup_start_date DESC