About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices
Showing posts with label Denali. Show all posts
Showing posts with label Denali. Show all posts

Wednesday, 17 October 2012

SQL Server # Database Backup Report using T-SQL

Today, I am going to share few very useful scripts which will report us on Database Backup from different view points.

To get the List/History/Log of all the Successful Backups

SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,    
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
ORDER BY 
  b.backup_start_date DESC
GO


To get a list of all successful Backups taken till date for a particular Database



DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
 
SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
WHERE
  b.database_name = @DBName  
ORDER BY 
  b.backup_start_date DESC
GO

To get the List of all Databases which are not backed up till date



SELECT
  d.name [DB_Name]
FROM
  master.sys.databases d
LEFT JOIN msdb.dbo.backupset b
  ON b.database_name = d.name
WHERE
  d.database_id IS NULL


To get the List of all Databases which are not backed up since last X days



DECLARE @LastXDays AS INT = 1
;WITH LatestBackupSet AS (
SELECT 
  b.database_name as DBName,
  b.backup_start_date LastBackedUpOn,
  b.[user_name],
  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM 
  msdb.dbo.backupset AS b
)
SELECT 
  lbs.DBName,
  lbs.LastBackedUpOn,
  lbs.[user_name]
FROM 
  LatestBackupSet AS lbs
WHERE
  DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays  
  AND lbs.Rnk = 1
ORDER BY 
  lbs.DBName DESC
GO


To get a list of the Latest successful backups of all Databases



;WITH LatestBackupSet AS (
SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location,
  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
)
SELECT 
  machine_name,
  server_name,
  DBName,
  backup_start_date,
  backup_finish_date,
  Backup_Type,
  expiration_date,
  [user_name],
  Total_Time_in_Minute,
  recovery_model,
  Total_Size_GB,
  Location
FROM 
  LatestBackupSet AS lbs
WHERE
  lbs.Rnk = 1
ORDER BY 
  lbs.DBName DESC
GO


To get the Latest successful backup of a particular Database



DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
 
;WITH LatestBackupSet AS (
SELECT 
  b.machine_name,
  b.server_name,
  b.database_name as DBName,
  b.backup_start_date,
  b.backup_finish_date,
  CASE 
    WHEN b.[type] = 'D' THEN 'Database'
    WHEN b.[type] = 'I' THEN 'Differential database'
    WHEN b.[type] = 'L' THEN 'Log'
    WHEN b.[type] = 'F' THEN 'File or filegroup'
    WHEN b.[type] = 'G' THEN 'Differential file'
    WHEN b.[type] = 'P' THEN 'Partial'
    WHEN b.[type] = 'Q' THEN 'Differential partial'
    ELSE b.[type]
  END Backup_Type,
  b.expiration_date,
  b.[user_name],
  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
  b.recovery_model,
  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
  bf.physical_device_name as Location,
  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM 
  msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
  ON b.media_set_id=bf.media_set_id
WHERE
  b.database_name = @DBName  
)
SELECT 
  machine_name,
  server_name,
  DBName,
  backup_start_date,
  backup_finish_date,
  Backup_Type,
  expiration_date,
  [user_name],
  Total_Time_in_Minute,
  recovery_model,
  Total_Size_GB,
  Location
FROM 
  LatestBackupSet AS lbs
WHERE
  lbs.Rnk = 1
ORDER BY 
  lbs.DBName DESC
GO


To get a list of Databases that were backed-up and do not currently exist



SELECT
  DISTINCT b.database_name
FROM
  msdb.dbo.backupset b
WHERE
  DB_ID(b.database_name) IS NULL

Hope, the above given script will be of help to you. Also, I would like to request you to please add any relevant script which you feel would be useful as a comment.

Posted by at 3 comments:
Labels: , , , , ,
Subscribe to: Posts (Atom)