Right or Wrong, Dynamic SQL AND a Cursor

,

OK, so I'm no big fan of dynamic SQL or cursors, however there are times where they do the job.  

Case in point, today I had to restore a database and a bunch of transaction logs.  To do this I started by manually restoring the database but because I had a bunch of transaction logs I felt I would feel the time would be better spent writing a quick transaction log restore script.  I know this will occur in the future and I like about having the necessary control over the code.  

set nocount on
declare @Database        varchar(200) = '[<your database here>]'
declare @FileDirectory   varchar(200) = 'E:\Transfer\TransactionLogs\'
declare @DirCMD          varchar(8000) = 'dir /b /o:d ' + @FileDirectory + '<database_Name_here>_backup_*.trn'
declare @TransactionLogs table (ID       int primary key identity(1,1)
                               ,FileName varchar(500)
                               )
declare @TransactionLogFile     varchar(500)
declare @SQL                    varchar(max)
declare @CRLF                   char(2) = char(13) + char(10)
declare @FullyQualifiedFileName varchar(200)

insert into @TransactionLogs (FileName)
exec xp_cmdshell @DirCmd
delete from @TransactionLogs where FileName is null

declare TransLog_Cursor cursor for
select FileName
from @TransactionLogs
order by ID

open TransLog_Cursor
fetch next from TransLog_Cursor into @TransactionLogFile
while @@FETCH_STATUS = 0
begin
	set @FullyQualifiedFileName = @FileDirectory + @TransactionLogFile
	set @SQL  = 'RESTORE LOG '     + @Database                      + @CRLF
    set @SQL += 'FROM  DISK = N''' + @FullyQualifiedFileName + '''' + @CRLF 
    set @SQL += 'WITH  FILE = 1'                                    + @CRLF
    set @SQL += ',  NORECOVERY'                                     + @CRLF
    set @SQL += ',  NOUNLOAD'                                       + @CRLF
    set @SQL += ',  STATS = 10'                                     + @CRLF
	print 'Restoring Transaction File: ' + @TransactionLogFile
	exec (@SQL)
	print '-- -- --'

	fetch next from TransLog_Cursor into @TransactionLogFile
end
close TransLog_Cursor
deallocate TransLog_Cursor

set @SQL = 'restore database [Integration_Layer_20131007] with recovery'
exec (@SQL)

Rate

2.67 (3)

Share

Share

Rate

2.67 (3)