SQL Server 2008/2012: Automate restore all transaction log backup files

Hi,
It's been a long time since I posted something on this blog. Had been working a lot on the IBM DB2 databases for the last couple of years. So it was business as usual in SQL Server. Hence, nothing noteworthy in SQL Server to blog about. But here's one script that saved me several hours yesterday.

On one of my servers we had to recover 10 databases using last night's full backup and all subsequent log backups. So in the middle of the night it was going to be huge challenge to manually pick every log backup file to restore.

So wrote this script to create commands to RESTORE log backup files which can be then run in a query window.

Please follow the instructions below to use the script.

1) Set the database name to the variable @dbname
2) Copy all the transaction log backups to a new directory. You can choose to start copying the files
    that  were taken a few minutes before the full backup was taken. You can choose to copy the last
    file you want to be restored based on the recovery point of time.
3) In the following line set the directory where you copied the log backup files to.

    insert into #dir
    exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'

4) In the following line set the directory to where the log backup files have been copied.
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM  DISK = 
        N''C:\Backup\'+@filename+''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
select (@cmd)

5) Copy the output which should have the RESTORE commands and run them in a new query    
    window.
6) Lastly run the following command to bring the database out of restoring state.

RESTORE DATABASE dbname WITH RECOVERY

############################################################################
use master
set nocount on
DECLARE @filename varchar(2000), @cmd varchar(8000), @dbname varchar(100)

-----------------------Enter the Database name in the next line
SET @dbname='New'

IF  EXISTS (SELECT name FROM tempdb.sys.tables WHERE name like '#dir%') 
begin
   DROP table #dir
end
create table #dir (filename varchar(1000))

-----------------------Enter the path to TRN File in the xp_cmdshell line
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'

delete from #dir where filename is null
DECLARE filecursor CURSOR FOR 
select * from #dir order by filename asc

OPEN filecursor
FETCH NEXT FROM filecursor INTO @filename 

WHILE @@FETCH_STATUS = 0 
BEGIN
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM  DISK = N''C:\Backup\'+@filename+''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
print @cmd
FETCH NEXT FROM filecursor INTO @filename
END 
CLOSE filecursor  
DEALLOCATE filecursor
drop table #dir

############################################################################