/*
This script will search for a particular LSN number within a directory of .trn files. The log file must use .trn for the
extension. It will also return ALL the infromation for every file if you enter a 0(zero) for the search lsn.

Written by kenneth.gore@gmailDOTcom
Use as you wish, modify as you wish..................
Need more? Email me :)
*/

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
set nocount on


declare   @restoreFromDir   varchar(255) = '\\Directory\Path\Without\backslash' --Holds the location of the .trn files
declare   @SearchLSN	    Float    = 285113000000004600001 --Holds the lsn you are searching for. A value of 0 to diplay ALL the information from every file.
declare   @DisplayasWeGo	bit	= 1 -- Determines wether information for each file will be displayed as its found or JUST the file that you are searching for at the end of the query.


if exists 
(
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
    and o.id = object_id(N'tempdb..#dirList')
)
DROP TABLE #dirList;

if exists 
(
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
    and o.id = object_id(N'tempdb..#filelist')
)
DROP TABLE #filelist;


declare	  @filename         varchar(100),
		  @cmd              varchar(500)


create table #dirList (filename varchar(255))

create table #filelist ( 

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

select @cmd = 'dir /b /on "' + @restoreFromDir+ '"'
insert #dirList exec master..xp_cmdshell @cmd  

--select * from #dirList where filename like '%.trn%' --order by filename

declare BakFile_csr cursor for 
  	select * from #dirList where filename like '%.trn%' --order by filename


--select * from #dirList where filename like '%.trn%'

open BakFile_csr
fetch BakFile_csr into @filename

while @@fetch_status = 0
   begin
       select @cmd = "RESTORE HEADERONLY FROM DISK = '" + @restoreFromDir + "\" + @filename + "'" 

       insert #filelist exec ( @cmd )

	   if @DisplayasWeGo = 1
			exec ( @cmd )

       PRINT '' 
       PRINT 'Getting information from ' + @filename

       fetch BakFile_csr into @filename

   end -- BakFile_csr loop

close BakFile_csr
deallocate BakFile_csr

if @SearchLSN = 0
	select * from #filelist
Else
	select * from #filelist where @SearchLSN BETWEEN FirstLSN AND LastLSN

drop table #dirList
drop table #filelist
return
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

quote:

---

Originally posted by Peter2012

Hi Gurus,

We've configured MS SQL Server 2008 R2 and restored the recent backup of PRD database (.bak file) into our DR server, for instance: PRD DB backup of 14 Oct 2012.

There are some transaction log files (*.TRN) that we'd like to read and find out information related to LSN (first LSN, last LSN, Checkpoing LSN, DatabaseBackup LSN, etc).

We know that it can be done by using this command:
RESTORE HEADERONLY FROM DISK='d:\Temp\WIR_20121014210000.trn'

However, this only read 1 TRN file, is it possible to use SQL query to ready a bunch on TRN files and generate an output containing LSN related information?

Appreciate for your help.

Thanks.




Cheers,
Peter

---