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.

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:

• Overview of Sql

• Sql server while loop

• Continue Statement in sql

• How to Create Sql Function,

• SQLInjection, prevent asp.net web application

• SQL Parameter how to use in asp.net

• SQL-QUERY TYPES, ALIASES, JOINS, CLAUSES, FUNCTIONS

• Sql-determine the version and edition of SQL Server

• Sql -Stored procedure how to use

• Different Types of SQL Server Stored Procedures

• SQL-Create Stored Procedure in SQL Server:

• SQL Server -Create Stored Procedure with Parameter

• SQL Server- Database Backup and Restore History

• Sql server cursor declaration syntax example

• SQLServer questions for interview

• Sql server-How to Optimization cursor

• Interview Part 3

• Interview Part 4