SQL Server Backup History and Header Information:

-

SQL Server-Backup History and Header Information:

Backup History (SQL Server):

In this post we take a sql query and try to find the backup history of the database.



As we know that in SQL server has all recodes of all databases so any time if we want to get the database backup history then we easily find out this according to the database name.


SELECT     
   bkSet.Backup_Start_Date as BackupStartDate,
   bkSet.Backup_Finish_Date as BackupEndDate,
   MedFam.Physical_Device_Name AS BackupPath,
   MedSet.Software_Name AS SoftwareUsedForBackup,
   bkSet.User_Name AS BackupTakenBy,
   bkSet.Server_Name AS ServerName,
   bkSet.Database_Name As DatabaseName,
    CASE bkSet.Type     
WHEN 'L' THEN 'TransactionLogBackup'
WHEN 'D' THEN 'FullBackup'
WHEN 'F' THEN 'FileBackup'
WHEN 'I' THEN 'DifferentialBackup'
WHEN 'G' THEN 'DifferentialFileBackup'
WHEN 'P' THEN 'PartialBackup'
WHEN 'Q' THEN 'DifferentialPartialBackup'
     ELSE NULL END AS BackupType,

    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) ASBackupSizeInMB

FROM       
     
           msdb..BackupMediaFamily MedFam

INNER JOIN 

      msdb..BackupMediaSet MedSet ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID

INNER JOIN 
      msdb..BackupSet bkSet ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID

WHERE     
            bkSet.Database_Name = '<your databse name>’'
AND       
     
      bkSet.Backup_Finish_Date BETWEEN 'date range first' AND 'Date rang second’

ORDER BY   

      bkSet.Backup_Finish_Date DESC


We get fallowing details after Execution of this query.

  • BackupStartDate,
  • BackupEndDate,
  • BackupPath,
  • SoftwareUsedForBackup,
  • BackuptakenBy,
  • ServerName,
  • DatabaseName,
  • BackupType,
  • AsBackupSizeInMB.



SQL Server Backup History and Header Information:

Other Database backup related topic:

  • Backup and Restore History Tables
  • Transact-SQL Statements for Accessing Backup History
  • Database and Transaction Log Files
  • Media-Header Information
  • Backup-Header Information
  • Comparison of Media-Header
  • Backup-Header Information
  • Backup Verification
  • Related Tasks

SQL Database Related other Post:


Comments

Post a Comment