-- 20 List Backup files in Server directory
-- script to list backup files and give type and version information on them
-- this is safe to run, it does not perform any updates

-- These are three optional parameters to control the output
-- if you want to list a different directory other than the default backup directory
-- then set it here
declare @overridebackupDirectory nvarchar(1024) = ''  -- null string uses default, or enter override
declare @fileName nvarchar(255) = '' -- null string lists all files in directory, or enter single file name
declare @databaseName nvarchar(255) = '' -- null string selects all, or enter single database name
-- default settings
declare @BackupFileExpression_FULL nvarchar(10) = '%.bak'
declare @BackupFileExpression_TRN nvarchar(10) = '%.trn'
declare @UseAllFiles bit = 1 -- true, use all files, set to false if file extensions are standard
--

declare @RegistrySettings table (HKey nvarchar(255), Val nvarchar(1024))
declare @backupDirectory nvarchar(1024)
declare @backupFileList table ( backupFileName nvarchar(1204), idepth int, ifile int)

insert into @RegistrySettings
exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

select @backupDirectory = val from @RegistrySettings

if @overridebackupDirectory > ''
begin
  select N'Server Name is: '+@@servername as "Server Version and Backup File Directory Name __________________________________________________________________________"
  union all
  select N'Version is: '+ @@VERSION 
  union all
  select 'Default backup directory is: ' + @backupDirectory 
  union all
  select 'Default overridden by ' + @overridebackupDirectory 
  Set @backupDirectory = @overridebackupDirectory -- N'c:\backups'
end
else
  select N'Server Name is: '+@@servername as "Server Version and Backup File Directory Name __________________________________________________________________________"
  union all
  select N'Version is: '+ @@VERSION 
  union all
  select N'Default backup directory used: ' + @backupDirectory 
--

insert into @backupFileList
exec xp_dirtree @backupDirectory, 1, 1

--update @backupFileList
--set backupfileName = @backupDirectory + N'\' + backupfileName 

--select backupFileName from @backupFileList

-- now set up the detail temporary table
declare @dbName varchar(256);

if (select object_id(N'tempdb..#headers') ) is not null
begin
  Drop table #headers
end

create table #headers ( id int identity,BackupName varchar(256), BackupDescription varchar(256), BackupType varchar(256), 
ExpirationDate varchar(256), Compressed varchar(256), Position varchar(256), DeviceType varchar(256), 
UserName varchar(256), ServerName varchar(256), DatabaseName varchar(256), DatabaseVersion varchar(256), 
DatabaseCreationDate varchar(256), BackupSize varchar(256), FirstLSN varchar(256), LastLSN varchar(256), 
CheckpointLSN varchar(256), DatabaseBackupLSN varchar(256), BackupStartDate varchar(256), BackupFinishDate varchar(256), 
SortOrder varchar(256), CodePage varchar(256), UnicodeLocaleId varchar(256), UnicodeComparisonStyle varchar(256), 
CompatibilityLevel varchar(256), SoftwareVendorId varchar(256), SoftwareVersionMajor varchar(256), 
SoftwareVersionMinor varchar(256), SoftwareVersionBuild varchar(256), MachineName varchar(256), Flags varchar(256), 
BindingID varchar(256), RecoveryForkID varchar(256), Collation varchar(256), FamilyGUID varchar(256), 
HasBulkLoggedData varchar(256), IsSnapshot varchar(256), IsReadOnly varchar(256), IsSingleUser varchar(256), 
HasBackupChecksums varchar(256), IsDamaged varchar(256), BeginsLogChain varchar(256), HasIncompleteMetaData varchar(256), 
IsForceOffline varchar(256), IsCopyOnly varchar(256), FirstRecoveryForkID varchar(256), ForkPointLSN varchar(256), 
RecoveryModel varchar(256), DifferentialBaseLSN varchar(256), DifferentialBaseGUID varchar(256), 
BackupTypeDescription varchar(256), BackupSetGUID varchar(256), CompressedBackupSize varchar(256));

Declare @MajorVersion int

SELECT @MajorVersion =
PARSENAME(CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')),4) 

If @MajorVersion >= 11
begin
	-- need containment column for "backup header only" command in SQL2012 and later
	-- as SQL Server versions beyond SQL 2014 RTM add columns to the "backup header only" command
	-- similar conditional code must be used to add them
	alter table #headers add Containment varchar(256) ;
end
-- you can run this or use the secondary script which nicely formats the output
-- execute ('RESTORE HEADERONLY FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\fpscdb009.bak''')

declare @cmd Nvarchar(1000)
declare @errors table ("Some Errors Occurred Reading Backup Headers" varchar(50), err_no int, error_message varchar(4000)
, "Error Command -------------------------------------------------------------------------------------------------------" nvarchar(1000));

Declare @backupFile nvarchar(1024)
,@backupId int

DECLARE backupFiles CURSOR FOR  
   SELECT backupFileName  
   FROM @backupFileList 
   where (@fileName = '' or backupFileName = @fileName)
   and (backupFileName like @BackupFileExpression_FULL or backupFileName like @BackupFileExpression_TRN or @UseAllFiles = 1 )
OPEN backupFiles  

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  

WHILE @@FETCH_STATUS = 0  
BEGIN 
  begin try 
      set @cmd = N'restore headeronly from disk = '''+ @backupDirectory + N'\' + @backupFile +N''''
	  insert into #headers 
	  exec(@cmd);
  end try
  begin catch
    -- swallow the error
        insert into @errors
		select @backupFile ,ERROR_NUMBER(), ERROR_MESSAGE(), @cmd;

  end catch
  select @backupId = SCOPE_IDENTITY()
  update #headers set BackupDescription = @backupFile  + case when BackupDescription IS not null then ' - '+BackupDescription else '' end where id = @backupId 
  FETCH NEXT FROM backupFiles INTO @backupFile  
END 

CLOSE backupFiles

--
SELECT backupDescription as "Backup File Name and Description"
--,backupName
,databaseName as original_databaseName
,case backuptype
when 1 then 'Full Database Backup'
when 2 then  'Transaction Log Backup'
when 4 then 'File Backup'
when 5 then 'Differential database Backup'
when 6 then 'Differential file Backup'
when 7 then 'Partial Backup' 
when 8 then 'Differential partial Backup'
else 'Unknown Backup Type' end as BackupTypeDescr
,str(softwareVersionMajor,2)+'.'+ str(softwareVersionMinor,2) as Version
,case when softwareVersionMajor = '7' then 'SQL Server 7'
when softwareVersionMajor = '8' then 'SQL Server 2000'
when softwareVersionMajor = '9' then 'SQL Server 2005'
when softwareVersionMajor = '10' and softwareversionminor < 50 then 'SQL Server 2008'
when softwareVersionMajor = '10' then 'SQL Server 2008 R2'
when softwareVersionMajor = '11' then 'SQL Server 2012'
when softwareVersionMajor = '12' then 'SQL Server 2014'
ELSE 'Unsupported SQL Server Version' + str(softwareVersionMajor) end as Backup_Format_Version
,RecoveryModel
,collation
--,compatibilityLevel
,case compatibilitylevel
when 80 then 'SQL Server 2000'
when 90 then 'SQL Server 2005'
when 100 then 'SQL Server 2008 and SQL Server 2008 R2'
when 110 then 'SQL Server 2012'
when 120 then 'SQL Server 2014' 
else 'Unknown check http://msdn.microsoft.com/en-us/library/bb510680.aspx' end as database_compatibility_description
,BackupFinishDate
,DATEDIFF(MINUTE,BackupStartDate,BackupFinishDate) As "Backup Duration in Minutes"
FROM #headers
where @databaseName = '' or DatabaseName = @databaseName
order by original_databaseName, BackupFinishDate desc

if (select count(*) from @errors) > 0
begin
  select * from @errors
end
