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.

set nocount on;

declare	@physical_location	varchar(80),
	@file_extension		char(3),
	@cmd_exec		nvarchar(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_information	varchar(80)  null)

--get everything from t-log source directory
insert	into #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;',
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
	--select @cmd_exec
	print 'Restoring: ' + @tlog
	--exec sp_executesql @cmd_exec
	fetch next from restore_tlog into @cmd_exec, @tlog

close restore_tlog
deallocate restore_tlog
drop table #dir_output
drop table #transaction_logs



4.5 (4)




4.5 (4)