Posted by Greg D. Moore \(Strider\) on 05/26/05 04:20

<Ty.Incog@gmail.com> wrote in message
news:1117045734.520071.73720@g43g2000cwa.googlegroups.com...
> Let's throw log shipping and clustering out of the conversation. Is
> there no automated way for me to restore a transaction log. Lets say a
> company only does one full backup a week on Sunday at midnight, but
> backs up the transaction log every 10 minutes. The database farts and
> dies on Saturday. That means there are approximately 6 logs/hr x 24
> hr/day x 6 days =864 logs to restore. Microsoft provided no way to
> apply these logs? The only way I can see to accomplish this is using
> RESTORE HEADERONLY to find the base LSN from the db backup, then using
> RESTORE HEADERONLY on the log backup and iterating through each record
> in the result set that has an LSN usable based upon the backup LSN.

I don't know why you'd care about the LSNs at this point. Simply grab the
first log file after the DB backup has completed.


>
> Sounds like multiple days of coding and tinkering to get that script
> working. Does anyone have a script already written? Does good 'ol
> Mircosoft provide one?

Replace the database_name and \\remote_drive\share1 with your own database
name and the disk where transaction log backups are stored.

This will generate a table with them listed in order with the restore syntax
added in, etc.

Simply cut and paste from the point you need forward.

Works great.

Bill is in the mail :-)


-- update dbname and directory and this will generate a quick list of
commands to restore backups.

create table #log_table
(
backupfile varchar(512)
)

create table #command_table
(
restore_commands varchar(1024)
)
go

declare @dbname sysname
declare @directory sysname
declare @cmdshellstring varchar(512)


select @dbname='Database_name'
select @directory='\\remote_drive\share1'
select @cmdshellstring = 'dir ' + @directory + @dbname + '\*.trn /od /b'
insert #log_table execute master..xp_cmdshell @cmdshellstring


select 'restore log '+ @dbname + ' from
disk='''+@directory+@dbname+'\'+backupfile +''' with norecovery' from
#log_table


select * from #command_table

select * from #log_table where backupfile like '%.bak' order by backupfile

drop table #log_table
drop table #command_table

>
> Regards,
>
> Ty
>

[Back to original message]