SQL Server Performance

Backup of Secindary server

Discussion in 'SQL Server Log Shipping' started by AKM, Sep 16, 2005.

  1. AKM New Member

    Hi,

    Is it possible to backup the databases on secondary server while the logshipping is active? We use Veritas backup Exec to backup.

    Thanks
    AKM
  2. satya Moderator

    Yes you can take backup of STANDBY database and use Veritas to backup that file.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. AKM New Member

    Hi satya,

    Is it safe backing up the database using Enterprise Manager and then backup the file using Veritas backup? Log shipping is scheduled to run between 4am to 8pm. I have scheduled the database backup(Using Enterprise manager) at 10pm.

    Thanks

    AKM
  4. mulhall New Member

    Yes, just make sure Veritas ignores the active database files and backs up the backup files.
  5. satya Moderator

    As you've schedule the backup it is managed by SQLAgent and need not to worry how it is going to process. As suggested just choose Veritas to take care of backup file and not any other files, I hope the backup will be stored on a seperate drive and directory.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. AKM New Member

    Thanks Satya and Mulhall.
  7. AKM New Member

    Hi Satya,

    I had scheduled the backup of database on standby system last night and it failed.
    The error is as follows:

    Executed as user: Domain<sid>adm. Database 'SID' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed. [SQLSTATE 42000] (Error 3036) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

    When I look at the error 3036, it says - "Your standby database has not been recovered and may not be backed up. Usually, you recover this database only if your primary fails and you switch operations to the standby. Until this occurs, rely on backups taken from the primary server."

    I am not sure if we could backup the database on standby system.

    Any suggestions.

    --AKM

  8. satya Moderator

    AKM
    Apologies for forgetting about a bit of process on secondary database to take full backup, this involves breakup of log shipping process. The reason behind this is :
    The Log Shipping process leaves the database on the secondary server in either a STANDBY or a LOADING status depending upon which option you select when you configure Log Shipping. Both STANDBY and LOADING are read-only states, which prevents any writes to the transaction log. Because the backup process needs to log its operations (backup history), and changes to the transaction log are disallowed, SQL Server does not allow you to perform backups performed against a log shipping secondary database.

    At my end we follow the process as:
    - Apply last log shipping backup to secondary database using WITH RECOVERY clause
    - Then perform full database backup on secondary server
    - Then before log shipping starts, a backup sync. process between primary and secondary server will take care of backups.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. AKM New Member

    Hi Satya,

    I have configurred Log Shipping using Enterprise Manager' Database Maintenance Planner. I am not sure if I could restore the last T-log in recovery mode, backup the secondary database and backup sysnc process between primary and secondary before log shipping starts at 4am.

    Any ideas?

    --AKM
  10. satya Moderator

    What is the interval of log shipping for these maintenance plans?
    In your case if the veritas can look up for primary server and then choose the full database backup from this server and leave secondary server as is.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. AKM New Member

    Interval is 30 minutes. We backup primary server using veritas backup exec. We were trying to backup the secondary server using Enterprise Manager without making any changes in Logshpping.

    Thanks
    Amrendra
  12. satya Moderator

    As you're performig Veritas backup from primary then why to backup secondary server database as well, as the databases are in sync in between.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. AKM New Member

    Just to be safer side for secondary server. I think we will have to rely on the backup of Primary server.

    Thanks
    AKM
  14. satya Moderator

    If you have secured the primary server backup then need not worry about secondary server, otherwise you must have to break the log shipping process in order to accomplish your task. Otherwise don't worry much about it and ensure to maintain similar versions of SQL & OS in between them.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. AKM New Member

    Thanks Satya.

    --AKM

Share This Page