Is there a way to restore the latest logs?

So I've brought a DB current and need to download logs and continue to bring the DB to current.

The log download directory is sync'd and will contain all the logs from the past as well as the un-restored logs. I want to be able to restore those and bring the DB current. Is there a way to do this? I want to automate this process.
Tagged:

Answers

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi, thank you for your forum post.

    I suspect that you need to setup something like Log Shipping. This help article explains the process and the steps using the Log Shipping wizard with SQL Backup:
    https://documentation.red-gate.com/sbu9/log-shipping

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • VindaliuVindaliu Posts: 3 New member
    Hello Eddie,

    Thanks for replying, but it is not setting up your traditional log shipping per se.

    The primary DB is actually not under my control. I received a full backup and then a differential, then after that I will only receive log files dropped off in a network share. There is no log shipping configuration, just the full, diff and logs. After I bring the secondary (DB under my control) current, I need to continue to bring it to a current state by restoring the logs. Is there a way to do that through command line? Thank you!
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi Vindaliu

    OK, now I fully understand.

    Simply restore the Full backup using the NORECOVERY or WITH STANDBY recovery model.

    Restore the latest differential using the NORECOVERY or WITH STANDBY recovery model.

    Create a new SQL Agent Job to perform the log restore, below is an example syntax you can modify:
    DECLARE @errorcode INT 
    
    DECLARE @sqlerrorcode INT
    
    EXECUTE master..sqlbackup '-SQL "RESTORE LOG [Database_name] FROM DISK = ''<path to sqb file location>\*.sqb'' WITH NORECOVERY, MOVETO = ''D:\RestoreCompleted''"', @errorcode OUT, @sqlerrorcode OUT;
    
    IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
    END
    

    Replace Database_name with the name of your database. *.sqb, * is the wildcard character, so if you have two or more files in the FROM DISK folder, providing SQL backup can read the LSN information in the backup file header, it will restore the log backup files in the correct sequence.

    <path to sqb file location> is the UNC path if using a network share or path to a local where the log backup files are located for restore.

    MOVETO command moves the successfully restored file to another located so it does not interfere with the next time the job is run.

    Alternatively, use the Scheduled Restore Job wizard available in the SQL Backup GUI to create the required job. More information here:
    https://documentation.red-gate.com/sbu9/restoring/scheduling-restore-jobs

    Many Thanks
    Eddie



    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • VindaliuVindaliu Posts: 3 New member
    Eddie, thank's a lot. I will try this.
Sign In or Register to comment.