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.

Read More

SQL Server # TSQL to Convert STRING in PROPER format

Problem Statement

SQL Server has got in-built functions to convert the given string into LOWER() or UPPER() format but it does not provides any direct way to convert it to PROPER format. A string/text is said to be in a PROPER format if all the words in that string/text starts with a CAPITAL letter.

E.g. If a string is - “Hello, how are you?”,

String in Lower format = “hello, how are you?”

String in Upper format = “HELLO, HOW ARE YOU?”

and String in Proper format = “Hello, How Are You?”

 

Implementation

Ideally, SQL Server is not the right place to implement this kind of logic, as string operations are costlier in SQL from performance perspective. it should be either implemented in the Front-End language or the Reporting Tool as this more related to the formatting. However, if this is to be implemented in SQL, the more preferred way is to use SQL-CLR function. It does not mean that we can not achieve this with T-SQL.

Today, I will share a simple T-SQL function, which could be used to convert any given string in PROPER format. Below is the script -

CREATE FUNCTION [dbo].[PROPER]

(

  @StrToConvertToProper AS VARCHAR(MAX)

) 

RETURNS VARCHAR(MAX) 

AS

BEGIN

  --Trim the Text

  SET @StrToConvertToProper = LTRIM(RTRIM(@StrToConvertToProper))

  --Find the No. of Words in the Text

  DECLARE @WordCount AS INT

  SET @WordCount = LEN(@StrToConvertToProper) - LEN(REPLACE(@StrToConvertToProper,' ','')) + 1

  --Variable to track the space position

  DECLARE @LastSpacePosition AS INT = 0

  --Loop through all the words

  WHILE(@WordCount > 0)

    BEGIN

      --Set the Space Position

      SET @LastSpacePosition = CHARINDEX(' ',@StrToConvertToProper,@LastSpacePosition + 1)

      --Replace the Character

      SET @StrToConvertToProper = STUFF(@StrToConvertToProper,

                                        @LastSpacePosition + 1,

                                        1,

                                        UPPER(SUBSTRING(@StrToConvertToProper, @LastSpacePosition + 1, 1)))

      --Decrement the Loop counter                                      

      SET @WordCount = @WordCount - 1

    END

  RETURN @StrToConvertToProper

END  

When the above script is used as –

SELECT dbo.PROPER('hello, how are you?')

we get the following result - Hello, How Are You?

Conclusion

The given script could be used to convert any string in PROPER format using T-SQL. However, I would personally prefer converting the string at the Front-End or in the Reporting tool to display the string in this format.

Read More