Recover transactions to standby database to a point in time

Jerrys0812Jerrys0812 Posts: 13
edited June 4, 2007 10:29PM in SQL Backup Previous Versions
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?
Jerry Sommerville
IS Support, DBA
Crown Equipment Corp
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Jerrys0812Jerrys0812 Posts: 13
    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?
    Jerry Sommerville
    IS Support, DBA
    Crown Equipment Corp
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    If that approach meets your requirements for the standby database, I see nothing wrong with it.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.