programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Website by URL, How to Play YouTube Videos in by using Jquery , Jquery Change div background on mouseover in , JQuery UI Datepicker (Calendar) etc.

Monday, November 3, 2014

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.

   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

           msdb..BackupMediaFamily MedFam


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

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

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


      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:

No comments:

Post a Comment