Transaction

[R7Dave]

Hello

I am trying to identify which transaction logs are not correct to use.

The BAK & 100s of TRN files are delivered on an external hard drive and restored here.

The following works...

if object_ID('tempdb..#x') is not null begin drop table #x end
create table #x (BackupName varchar(1000),
   BackupDescription varchar(1000),	
   BackupType int,	
   ExpirationDate datetime ...this is part of the table definition


INSERT INTO #x
EXEC('RESTORE HEADERONLY FROM disk =N''J:\DCF_TLOG\NJNEW_20110601204503.trn''')

How can I get the result set of #x to include the physical file name? I want "NJNEW_20110601204503.trn" in the result set.

So the results would look like this...

PhysicalFileName J:\DCF_TLOG\NJNEW_20110601204503.trn
BeginsLogChain 0
FirstLSN 8432383000019020400001
LastLSN 8432383000019145000001
CheckpointLSN 8432383000017737300001
DatabaseBackupLSN 8366423000000630700043


Thanks in advance
Dave

 

[simian336]

If it was me I would just drop it on the previous record...

if object_ID('tempdb..#x') is not null
begin
drop table #x
end

create table #x (
BackupName varchar(1000),
BackupDescription varchar(1000),
BackupType int,
ExpirationDate datetime)

INSERT INTO #x (BackupName) values ('J:\DCF_TLOG\NJNEW_20110601204503.trn')

INSERT INTO #xEXEC('RESTORE HEADERONLY FROM disk =N'
'J:\DCF_TLOG\NJNEW_20110601204503.trn''')


Simi

 

[R7Dave]

Thanks Simi

Is there a way to put them in the same row?

Right now its a manual process and I want to figure out which files do not belong - so after I create the temp table I can say "select * from #x where..." and figure out the logic with the LSN - so I can manually remove the files that don't belong.

Thanks
Dave

 

[simian336]

Just thiking out loud here... If there was a number field in the table, you could insert a number in the field then update the table based on the number.

update #x
set BackupName= 'PhysicalFile J:\DCF_TLOG\NJNEW_20110601204503.trn') + NameBackupname
where Number_field = last_insert_number

Simi