Is there a way to restore the latest logs?

Vindaliu · October 13, 2017 5:18PM

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.

Eddie D · October 16, 2017 12:47PM

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

Vindaliu · October 16, 2017 1:32PM

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 D · October 17, 2017 10:32AM

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

Vindaliu · October 17, 2017 10:08PM

Eddie, thank's a lot. I will try this.

Is there a way to restore the latest logs?

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk