Automation of TLOG Restoration

  • Wecks

    SSChasing Mays

    Points: 644

    I have a folder location on a SQL server that has daily TLOG backups residing in the side that gets added to daily.

    I need a script that I can run that I can point at the folder location and will restore the latest TRN file to a database.  I don't want to specify the name of the tlog I am restoring as there will be multiple trn files located inside with different names i just want to restore the latest one.

     

     

     

     

    • This topic was modified 5 days, 8 hours ago by  Wecks.
  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • rvsc48

    SSCertifiable

    Points: 7178

    Hi, Log shipping does these exact steps.  If you have already considered Log Shipping, what different behavior are you looking for?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Are you keeping a database in norecovery mode (or standby)? IF so, log shipping would be easier.

    Are you removing old log files from here? There is no good way to just know which file to restore. A script has to walk through them all, or has to know what has already been restored to a database.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88052

    If you want to be able to list the latest file in a directory - you can use Powershell:

    PS> dir | Select -Last 1

    If there are other file types - you can filter them using -Exclude or through the pipe-line with a where:

    PS> dir -Exclude *.bak | Select -Last 1
    PS> dir | ? {$_.Extension -ne 'bak'} | Select -Last 1

    You can expand on this to use Invoke-SqlCmd to query the destination to check if that file has already been restored and even perform the restore.  Lots of possibilities...

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Wecks

    SSChasing Mays

    Points: 644

    Hi Steve,

     

    So the background is the log files are being Tlog shipped from an external company once a day to a file share.  This external company has provided us with the latest full backup and the logs to bring the DB up to date and will continue to provide tlog files on a schedule of once a day which will need to be restored in standby mode so that the data can still be accessed in read-only mode.  The issue is that they don't want to have to restore the logs manually.

    The script needs to check a local location which will contain lots of logs and will restore the latest one.

     

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    OK, the process I'd follow is to treat these like ETL. I want to "load" the files and then move them.

    My script would need to:

    • Find the next file to load. This is likely some restore from filelistonly that determines the next LSN I need.
    • Load this file
    • move this file to another folder
    • repeat

    You can check restorehistory, which should have the last file restored. If the files are named correctly, scripting is easier, but still need to scan them to find the next file to restore.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply