Competition: What’s your favorite Redgate tool? Enter now.

Log Shipped Database left restoring

simmonsj_98simmonsj_98 Posts: 18
edited July 23, 2010 2:26PM in SQL Backup Previous Versions
I am trying to set up log shipping and have the following problem. The log is copied and restored, and move into the processed folder. If I execute RESTORE DATABASE <name> WITH RECOVERT it will go into an operational state. The problem is that after a log is restore it stays in a restore state, and doesn’t go into a Standby/Readonly state. Anyone else seen this, or have any ideas of what I may be doing wrong?

Comments

  • peteypetey Posts: 2,358 New member
    If you used the SQL Backup GUI to set up your log shipping process, then in step 3, you have the option to set the destination database to read-only mode, in the 'Destination database restore options' section. The default option is non-operational.

    If you have already set up the log shipping process, then on the secondary server, you can edit the restore job, and edit the restore process to use the STANDBY option e.g.

    from

    EXEC master..sqlbackup '-sql "RESTORE LOG ... WITH ... NORECOVERY ..." '

    to

    EXEC master..sqlbackup '-sql "RESTORE LOG ... WITH ... STANDBY = [g:\AdventureWorks.und] ..." '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I thought I set it to readonly on step 3, but I gusee I didn't. Recreated the job making sure it was set, and it is working.
Sign In or Register to comment.