Recover transactions to standby database to a point in time

Jerrys0812

In my standby database environment, we backup transactions on the production database and ship them to the standby database without immediately applying the logs to the standby. We want to be able to apply all un-applied transactions up until a specific time. At all times, we want our standby database to be at least 4 hours behind production. How can we do that with SQL Backup?

Brian Donahue

Hi Jerry,

I believe this is possible with some modifications to the script. You could certainly create a log shipping job between the two servers, then modify the restore job to include the STOPAT tag, specifying a stop time four hours ealier than the current time.

WITH STOPAT=DATEADD(hour, -4, GETDATE())

This is assuming that SQL Backup leaves behind log files that exceed the STOPAT date value. If it doesn't, then the log files will get moved out of the restore share and you'll get an LSN out-of-order error at the next restore. I haven't tried this... Petey, over to you?

petey

Out of the box, the log shipping setup in SQL Backup isn't going to meet your needs for the following reasons:

- all log files in the restore folder are restored together. While you can modify the script to use the STOPAT option, SQL Backup doesn't stop restoring at the file that contains the STOPAT records. It will continue to restore all subsequent files.

- this in turn will raise SQL error 4305, where the subsequent log files are too late to be appled, since you used the STOPAT option to cut off the restore at an earlier time. While you can ignore this error, it might hide 'real' 4305 errors in the future.

- as mentioned by Brian, the file that contains the STOPAT records will be moved to the MOVETO folder. This will cause the subseqent restore to fail as you need this file for the next restore.

What needs to be done is the following, where:

folder A - folder containing all trx log backup files
folder B - folder where standby server will use for the restore

- for any restore, move all log backups up to the STOPAT time, excluding the file that contains the STOPAT records, from folder A to folder B

- restore these logs with the MOVETO option

- move the log backup containing the STOPAT records to folder B

- restore this log using the STOPAT option, but not the MOVETO option

This ensures that SQL Backup does not restore more logs that are necessary to meet the STOPAT criteria, and does not move the backup file containing the STOPAT logs, making it available for the next restore.

Jerrys0812

Thanks. We also thought of an idea. Do a full backup at 2:00am and restore it to the standy server at 6:00am. Then do a differential at noon and restore that at 4:00pm. Then, each 15 minutes do a transaction backup and keep those to bring the server up to date in the event of a crash. Your thoughts?

petey

If that approach meets your requirements for the standby database, I see nothing wrong with it.