Automated T-Log Restore
This is just a quick and dirty little script I put together for automating t-log restores to a stand-by database. In our case we have to download t-logs from a third party to use for reporting purposes. The restores were done in a monthly cycle and I got tired of doing it manually. This script is now a part of a SQL Agent batch job that runs nightly. The users download the t-logs and put them in a share whenever they want the db updated.
begin
set nocount on;
declare@physical_locationvarchar(80),
@file_extensionchar(3),
@cmd_execnvarchar(500),
@db_name varchar(100),
@tlog varchar(5000)
set @physical_location = '<directory_containing_transaction_logs>'
set @file_extension = 'log'
set @db_name = '[<database_to_restore_logs_to>]'
select@cmd_exec= 'dir '
+ @physical_location
+ '\*.'
+ @file_extension
--Include subdirectories + ' /S'
create table #dir_output
(file_informationvarchar(80) null)
--get everything from t-log source directory
insertinto #dir_output
exec master.dbo.xp_cmdshell @cmd_exec
--load t-log information into secondary temp table for processing
select database_name= @db_name,
backup_start_date= cast(substring(file_information,1,18) as datetime ),
physical_device_name= @physical_location
+ substring(file_information,40,charindex('System_',file_information) - 40 )
+ '\'
+ substring(file_information,40,80)
into#transaction_logs
from #dir_output
where file_information like '%.log'
--create cursor to loop through t-logs to be restored
--change command options depending on what you are using the script for
declare restore_tlog cursor for
select 'RESTORE LOG ' + database_name + ' FROM DISK = '''
+ physical_device_name
+ ''' WITH FILE = 1, '
--+ 'STANDBY = ''X:\<database>.ldf'', NOUNLOAD, STATS = 10;',
+ 'NORECOVERY;',
physical_device_name
from #transaction_logs
order by backup_start_date asc;
open restore_tlog
fetch next from restore_tlog into @cmd_exec, @tlog
-- loop through the list, depending on whats uncommented below you can
-- automatically restore the t-logs or generate the code so you can run the resTores manually
while @@fetch_status = 0
begin
--select @cmd_exec
print 'Restoring: ' + @tlog
--exec sp_executesql @cmd_exec
fetch next from restore_tlog into @cmd_exec, @tlog
end
close restore_tlog
deallocate restore_tlog
drop table #dir_output
drop table #transaction_logs
end 
