How to get Information about your Backups and Save into SQL Server Table - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or developer, you need to get Database Name from backups files or maybe other information such as

• Who performed this backup
• Which Server wrote this backup
• Size of backup in bytes
• Version of Database
• Either is backup is taken by compression enable or not
• Compatibility level of the database from which backup was created

and tons of other information. Not a big deal, We can use below statement to get above information.

RESTORE HEADERONLY FROM DISK='c:\backup\Yourbackupfile.bak'

That is great, but think about a situation when you have a lot of backup files and you would like to gather information and store in a table. 

The below script can be used to save RESTORE HEADERONLY information to table for all the backup files which exists in backup folder you have provided. 

 --provide the folder path where backups are present
 DECLARE @BackupFolder VARCHAR(128)
 SET @BackupFolder='C:\Backup\'

 --Get Backup files informaiton in temp table
  IF OBJECT_ID('tempdb..#BackupFileList') IS NOT NULL
    DROP TABLE #BackupFileList

     IF OBJECT_ID('tempdb..#RestoreHeaderOnly') IS NOT NULL
    DROP TABLE #RestoreHeaderOnly
--Create temp table to Store HeaderOnly Information from Database
Create Table #RestoreHeaderOnly(
BackupName nvarchar(128) ,
BackupDescription nvarchar(255) ,
BackupType smallint ,
ExpirationDate datetime ,
Compressed bit ,
Position smallint ,
DeviceType tinyint ,
UserName nvarchar(128) ,
ServerName nvarchar(128) ,
DatabaseName nvarchar(128) ,
DatabaseVersion int ,
DatabaseCreationDate datetime ,
BackupSize numeric(20,0) ,
FirstLSN numeric(25,0) ,
LastLSN numeric(25,0) ,
CheckpointLSN numeric(25,0) ,
DatabaseBackupLSN numeric(25,0) ,
BackupStartDate datetime ,
BackupFinishDate datetime ,
SortOrder smallint ,
CodePage smallint ,
UnicodeLocaleId int ,
UnicodeComparisonStyle int ,
CompatibilityLevel tinyint ,
SoftwareVendorId int ,
SoftwareVersionMajor int ,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(128) ,
Flags int ,
BindingID uniqueidentifier ,
RecoveryForkID uniqueidentifier ,
Collation nvarchar(128) ,
FamilyGUID uniqueidentifier ,
HasBulkLoggedData bit ,
IsSnapshot bit ,
IsReadOnly bit ,
IsSingleUser bit ,
HasBackupChecksums bit ,
IsDamaged bit ,
BeginsLogChain bit ,
HasIncompleteMetaData bit ,
IsForceOffline bit ,
IsCopyOnly bit ,
FirstRecoveryForkID uniqueidentifier ,
ForkPointLSN numeric(25,0) NULL ,
RecoveryModel nvarchar(60) ,
DifferentialBaseLSN  numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
BackupTypeDescription nvarchar(60) ,
BackupSetGUID uniqueidentifier NULL ,
CompressedBackupSize bigint ,
containment tinyint not NULL ,
KeyAlgorithm nvarchar(32) ,
EncryptorThumbprint varbinary(20) ,
EncryptorType nvarchar(32))


--create temp table to save backup files
--I am considering all backups are in root level
--if they are in sub folders, change your select query for #BackupFileList
  CREATE TABLE #BackupFileList (
    Id int identity(1,1),
    BackupFile nvarchar(255),
    Depth smallint,
    FileFlag bit)

   INSERT INTO #BackupFileList (BackupFile,Depth,FileFlag)
   EXEC xp_dirtree @BackupFolder, 10, 1

   --Use Cursor to loop throught backups files
   --to get header information and insert into temp table
Declare @BackupFile VARCHAR(500)

   DECLARE Cur CURSOR FOR
  SELECT BackupFile from #BackupFileList
  where fileflag=1

OPEN Cur
FETCH Next FROM Cur INTO @BackupFile
WHILE @@FETCH_STATUS = 0
  BEGIN

  insert into #RestoreHeaderOnly
  EXEC('RESTORE HEADERONLY FROM DISK = '''+@BackupFolder+@BackupFile+'''')


   FETCH Next FROM Cur INTO @BackupFile
  END
CLOSE Cur
DEALLOCATE Cur


--Check the Restore HeaderOnly information from temp table for all the backups files
select * From #RestoreHeaderOnly

I execute above query and it was able to get the information from backups files and insert into temp table. Here is partial snapshot as can't show all the columns.

How to get information such as Database Name,Version,Server Name from backup files and insert into SQL Server Table