Generate SQL Script to Restore all Transaction Logs After Last Database Backup

2014-08-20

If you have to reinitialize the log shipping for a database which is doing log backup every 5 minutes, there are probably piles of log backup files need to be restored after restoring database backup nightly. A little bit crazy if you have to manually go through all files. This is a helpful script to generate the SQL script for you to do all the log restoral.

<span class="kwrd">SELECT</span>  <span class="str">'RESTORE LOG ['</span> + database_name + <span class="str">'] FROM DISK='</span><span class="str">''</span>
        + physical_device_name + <span class="str">''</span><span class="str">' WITH NORECOVERY'</span>
<span class="kwrd">FROM</span>    msdb.dbo.backupset a
        <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> msdb.dbo.backupmediafamily b <span class="kwrd">ON</span> a.media_set_id = b.media_set_id
<span class="kwrd">WHERE</span>   database_name = <span class="str">'laFileStore'</span>
        <span class="kwrd">AND</span> type = <span class="str">'L'</span>
        <span class="kwrd">AND</span> backup_start_date >= ( <span class="kwrd">SELECT</span> <span class="kwrd">TOP</span> 1
                                            backup_start_date
                                   <span class="kwrd">FROM</span>     msdb.dbo.backupset
                                   <span class="kwrd">WHERE</span>    database_name = <span class="str">'laFileStore'</span>
                                            <span class="kwrd">AND</span> type = <span class="str">'D'</span>
                                   <span class="kwrd">ORDER</span> <span class="kwrd">BY</span> backup_start_date <span class="kwrd">DESC</span>
                                 )
<span class="kwrd">ORDER</span> <span class="kwrd">BY</span> a.backup_start_date