Richard

  • Home
All Posts Links About

Richard

  • Home

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 
  • SQL
  • SQL Server

扫一扫,分享到微信

微信分享二维码
SQL Server 2016 is coming
useLegacyV2RuntimeActivationPolicy fix for Crystal Report on .Net Framework 4
© 2017 Richard
Hexo Theme Yilia by Litten
  • All Posts
  • Links
  • About

tag:

  • ASPNET
  • DotNetCore
  • VS2015
  • Service Pack 2
  • SQL Server
  • Database Restore
  • SQL Server 2008 R2
  • GitHub
  • Blog
  • Trace Flags
  • SQL Server 2017
  • Linux
  • SQL Server Management Studio
  • SSMS
  • SQL Server 2014

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录(注意不是yilia根目录)执行以下命令:
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置:

      jsonContent:
        meta: false
        pages: false
        posts:
          title: true
          date: true
          path: true
          text: false
          raw: false
          content: false
          slug: false
          updated: false
          comments: false
          link: false
          permalink: false
          excerpt: false
          categories: false
          tags: true
    

  • SQL Server Updates
  • SQLBlog.com
  • SQL Server Central
  • My Old Blog Site 1
  • My Old Blog Site 2
  • Unicode Character Map for Windows
  • Dev Tool List (From Scott Hanselman)
Improve daily life using IT techs.