May 30, 2013 at 3:13 pm
I wrote some code that will basically search an entire directory of .trn files(Log ships in my case). It uses the RESTORE HEADERONLY function and inserts the results in to a temp table and then uses the BETWEEN function ,the FirstLSN column and the lastlsn column to find the LSN you are looking for. I'll post the code in its entirety below(In case someone would find it useful) but this is the query that actually does the aforementioned function using between more or less:
QUERY:
select convert(NUMERIC(38), FirstLSN) as 'FirstLSN', convert(NUMERIC(38), lastlsn) as 'lastlsn'
from #filelist where 17017000006794800011 BETWEEN FirstLSN AND LastLSN
RESULT:
FirstLSN lastlsn
1701700000576260100017017000006794799000 (WRONG)
1701700000679479900017017000007787600000 (RIGHT)
The #filelist table (Which contains the results of each HEADERONLY query) defines FirstLSN and LastLSN as FLOAT and the other fields as varchar. It returns the correct result but also it returns an additional record that seems to be erroneous. In the full version below I use a variable called @SearchLSN which also is defined as type FLOAT. This is necessary for the BETWEEN function as it only compares like types.
Anyone have any idea why BETWEEN would return two results with one being incorrect? The wrong record btw is always the record immediately before the correct record in the table.
Here is the code in its entirety in case its useful for someone(Please excuse the formatting):
/*
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) = '\\Place\Your\directory\path\here\without\backslash' --Holds the location of the .trn files
declare @SearchLSN Float = 17017000006794800011 --Holds the lsn you are searching for. A value of 0 to diplay ALL the information from every file.
declare @DisplayasWeGobit= 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 (
BackupNamevarchar(255),
BackupDescriptionvarchar(255),
BackupTypevarchar(255),
ExpirationDatevarchar(255),
Compressedvarchar(255),
Positionvarchar(255),
DeviceTypevarchar(255),
UserNamevarchar(255),
ServerNamevarchar(255),
DatabaseNamevarchar(255),
DatabaseVersionvarchar(255),
DatabaseCreationDatevarchar(255),
BackupSizevarchar(255),
FirstLSNFloat,
LastLSNFloat,
CheckpointLSNFloat,
DatabaseBackupLSNFloat,
BackupStartDatevarchar(255),
BackupFinishDatevarchar(255),
SortOrdervarchar(255),
[CodePage]varchar(255),
UnicodeLocaleIdvarchar(255),
UnicodeComparisonStylevarchar(255),
CompatibilityLevelvarchar(255),
SoftwareVendorIdvarchar(255),
SoftwareVersionMajorvarchar(255),
SoftwareVersionMinorvarchar(255),
SoftwareVersionBuildvarchar(255),
MachineNamevarchar(255),
Flagsvarchar(255),
BindingIDvarchar(255),
RecoveryForkIDvarchar(255),
Collationvarchar(255),
FamilyGUIDvarchar(255),
HasBulkLoggedDatavarchar(255),
IsSnapshotvarchar(255),
IsReadOnlyvarchar(255),
IsSingleUservarchar(255),
HasBackupChecksumsvarchar(255),
IsDamagedvarchar(255),
BeginsLogChainvarchar(255),
HasIncompleteMetaDatavarchar(255),
IsForceOfflinevarchar(255),
IsCopyOnlyvarchar(255),
FirstRecoveryForkIDvarchar(255),
ForkPointLSNvarchar(255),
RecoveryModelvarchar(255),
DifferentialBaseLSNvarchar(255),
DifferentialBaseGUIDvarchar(255),
BackupTypeDescriptionvarchar(255),
BackupSetGUIDvarchar(255),
CompressedBackupSizevarchar(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
BackupName,
BackupDescription,
BackupType,
ExpirationDate,
Compressed,
Position,
DeviceType,
UserName,
ServerName,
DatabaseName,
DatabaseVersion,
DatabaseCreationDate,
BackupSize,
convert(NUMERIC(38), FirstLSN) as 'FirstLSN',
convert(NUMERIC(38), lastlsn) as 'lastlsn',
convert(NUMERIC(38), CheckpointLSN) as 'CheckpointLSN',
convert(NUMERIC(38), DatabaseBackupLSN) as 'DatabaseBackupLSN',
BackupStartDate,
BackupFinishDate,
SortOrder,
[CodePage],
UnicodeLocaleId,
UnicodeComparisonStyle,
CompatibilityLevel,
SoftwareVendorId,
SoftwareVersionMajor,
SoftwareVersionMinor,
SoftwareVersionBuild,
MachineName,
Flags,
BindingID,
RecoveryForkID,
Collation,
FamilyGUID,
HasBulkLoggedData,
IsSnapshot,
IsReadOnly,
IsSingleUser,
HasBackupChecksums,
IsDamaged,
BeginsLogChain,
HasIncompleteMetaData,
IsForceOffline,
IsCopyOnly,
FirstRecoveryForkID,
ForkPointLSN,
RecoveryModel,
DifferentialBaseLSN,
DifferentialBaseGUID,
BackupTypeDescription,
BackupSetGUID,
CompressedBackupSize
from #filelist
Else
select 'The LSN searched for was found in the following transaction log backup.' as "Search Results",
BackupName,
BackupDescription,
BackupType,
ExpirationDate,
Compressed,
Position,
DeviceType,
UserName,
ServerName,
DatabaseName,
DatabaseVersion,
DatabaseCreationDate,
BackupSize,
convert(NUMERIC(38), FirstLSN) as 'FirstLSN',
convert(NUMERIC(38), lastlsn) as 'lastlsn',
convert(NUMERIC(38), CheckpointLSN) as 'CheckpointLSN',
convert(NUMERIC(38), DatabaseBackupLSN) as 'DatabaseBackupLSN',
BackupStartDate,
BackupFinishDate,
SortOrder,
[CodePage],
UnicodeLocaleId,
UnicodeComparisonStyle,
CompatibilityLevel,
SoftwareVendorId,
SoftwareVersionMajor,
SoftwareVersionMinor,
SoftwareVersionBuild,
MachineName,
Flags,
BindingID,
RecoveryForkID,
Collation,
FamilyGUID,
HasBulkLoggedData,
IsSnapshot,
IsReadOnly,
IsSingleUser,
HasBackupChecksums,
IsDamaged,
BeginsLogChain,
HasIncompleteMetaData,
IsForceOffline,
IsCopyOnly,
FirstRecoveryForkID,
ForkPointLSN,
RecoveryModel,
DifferentialBaseLSN,
DifferentialBaseGUID,
BackupTypeDescription,
BackupSetGUID,
CompressedBackupSize
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
May 30, 2013 at 3:49 pm
Keep in mind that float is an imprecise data type, and the value actually stored in the table may not be exactly the same as the value that was inserted.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply