locked
T-SQL Script to find the backup job details RRS feed

  • Question

  • Hi,

    I have a requirement to get the below details:

    1) Database Name

    2) Backup Job Name

    3) Database Type (Full/Differential/Log)

    4) Recovery Model

    5) Time of the SQL Backup Job.

    Please help me in getting all these things in single query as I have to collate information from many queries to get the information. We have 100+ servers and need to update this information.

    Thanks in advance!

    Regards,

    Sharath

     

     

     

    Tuesday, April 12, 2011 6:15 PM

Answers

  •  

    You can find your information by using this:

     

    Select	database_name As [Database Name]
    		, [name] As [Backup Name]
    		, Case	When Type = 'D' Then 'Full Backup'
    				When Type = 'I' Then 'Differential Backup'
    				When Type = 'L' Then 'Log Backup'
    				Else 'File or filegroup or partial'
    			End As [Backup Type]
    		, Recovery_Model
    		, [backup_start_date] As [Time of the SQL Backup Job]
    FROM	[msdb].[dbo].[backupset]

     

    Wednesday, April 13, 2011 12:01 AM

All replies

  • I read today this interesting new blog by Brad Schulz which may provide the information you're looking for

    Documenting your SQL Server Agent Jobs


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, April 12, 2011 6:23 PM
  •  

    You can find your information by using this:

     

    Select	database_name As [Database Name]
    		, [name] As [Backup Name]
    		, Case	When Type = 'D' Then 'Full Backup'
    				When Type = 'I' Then 'Differential Backup'
    				When Type = 'L' Then 'Log Backup'
    				Else 'File or filegroup or partial'
    			End As [Backup Type]
    		, Recovery_Model
    		, [backup_start_date] As [Time of the SQL Backup Job]
    FROM	[msdb].[dbo].[backupset]

     

    Wednesday, April 13, 2011 12:01 AM
  • DECLARE @db_name VARCHAR(100)
    SELECT @db_name = DB_NAME()


    SELECT TOP ( 30 )
    s.server_name,
    s.database_name,
    CASE s.[type]
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    END as BackupType,
    s.recovery_model,
    s.backup_finish_date,
    s.backup_start_date,
    DATEDIFF(mi,s.backup_start_date , s.backup_finish_date)/60 as Total_Time_in_Hours,
    cast(CAST(s.backup_size / 1024 / 1024 / 1024 AS INT) as varchar(14))
    + ' ' + 'GB' as bkSize,
    CAST(s.first_lsn AS varchar(50)) AS first_lsn,
    CAST(s.last_lsn AS varchar(50)) AS last_lsn,
    m.physical_device_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
    ORDER BY backup_start_date desc,
    backup_finish_date
    Thursday, April 3, 2014 8:18 AM