SQL Server Performance

How to make a backup of a database in Standby Mode?

Discussion in 'SQL Server 2005 General DBA Questions' started by dbaGuy, Aug 28, 2007.

  1. dbaGuy New Member

    How does one go about making a backup of a Standby database (which is receiving log shipments) for development purposes?
    In our case, I don't have the bandwidth to transport copies of backups from the primary location to the secondary location in a reasonable amount of time.
    Thanks in adance.
  2. jn4u Member

    Is possbile to make a backup of database in restore state? Can you make database backup with the COPY_ONLY option?
  3. ghemant Moderator

    Welcome to the forum!
    I didn't get your question!! Do you mean you wants to take backup of standby database!!
  4. satya Moderator

    Which edition of SQL ou are using?
  5. dbaGuy New Member

    SQL 2005 Standard Edition. I am trying to make a copy of the secondary database which is in standby mode to avoid having to ship full database backups over the wire as we have limited bandwidth.
    I've tried the following technique:
    1. Bring the secondary DB out of standby mode
    2. Make a full backup of the secondary DB
    3. Drop the secondary DB
    4. Restore the secondary DB
    The problem with this technique is that at step 2 the LSN chain is broken and the secondary appears to be become unique.
  6. satya Moderator

    When you configure Log Shipping through the SQL Server Enterprise Manager Database Maintenance Plan wizard, if you select NORECOVERY mode in the Add Destination Database dialog box, the database is loaded in STANDBY mode.
    No need to change recovery model for the secondary database from loading or standby to online, this will break the log shipping chain as per the configuration.
    http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx fyi and you can perform backup on the standby database as it perform read operations only. But ensure to run this outside of log shipping restore schedule.
  7. satya Moderator

    When you configure Log Shipping through the SQL Server Enterprise Manager Database Maintenance Plan wizard, if you select NORECOVERY mode in the Add Destination Database dialog box, the database is loaded in STANDBY mode.
    No need to change recovery model for the secondary database from loading or standby to online, this will break the log shipping chain as per the configuration.
    http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx fyi and you can perform backup on the standby database as it perform read operations only. But ensure to run this outside of log shipping restore schedule.
  8. dbaGuy New Member

    Hello Satya,
    Thank you for your reply. I'm very aware of the fact that log shipping breaks when the secondary database has a backup taken on it. However, I am curious to know exactly how you are able to take a backup of a database that is in Standby / Readonly mode? I don't see any way to do that. I must have missed it in the link you referenced. Can you post your steps please?
    Thank you
  9. ghemant Moderator

    Hi,
    AFAIK their is no way to take a backup of database that is in standby, but as alternative you may schedule creation of script with data on regular interval , schedule copy database.
  10. satya Moderator

    Instead of breaking the log shipping why not backup the primary database and use that backup to store on another location?
  11. dbaGuy New Member

    Satya,
    I would love to do that, except I'm in a low bandwidth situation so transfering the datbase (even in a compressed format) is problematic at best.
  12. satya Moderator

    Ok, revisiting your question again. You need to backup from the secondary database for development purpose. That means you are able to take thsi backup to another server for your development, in this regard why not take backup from primary server itself for the development work

Share This Page