SQL Server Performance

Backup a logshipping database in the secondary server

Discussion in 'SQL Server 2005 Log Shipping' started by WingSzeto, Nov 21, 2008.

  1. WingSzeto Member

    We are doing logshipping from Primary server A to secondary server B. Server A and server B are in two different cities (say city A and city B) and the backup and restore interval is every 15 minutes. We are doing the backup on server A and ftp the backup file back to city B to be put it on a backup media. This ftp process is gradually taking longer and longer time due to the the growth of the database. We are thinking an alternative to avoild the ftp process.
    Since we are already doing logshipping from city A to city B and the data in the secondary server B is only 15 minutes behind if there is a way we can back up the logshipping database in server B, we can use this to put on the backup media . The logshipping database is in a standby mode so I can't back up the database with that status. Is there a way I can back up the log shipping database so we can use it to do the restore later if diaster arises? Can I temporary change the status of a log shipping database somehow to do the backup and then change it back for the logshipping (I will disable the LS restore job before I do that)? Is there a back door non-support way?
    wingman
  2. MohammedU New Member

    I have not try this but only option would be...
    1. backup the master db from ServerB and Restore on to ServerC
    2. backup the msdb db from ServerB and Restore on to ServerC
    3. Stop the SQL Services on ServerB and ServerC and copy ldf and mdf of stanby db to ServerC
    4. Note make sure you have same folder structure on both Server B and ServerC before starting Step1.
    Restart the SQL Services on both servers...
  3. WingSzeto Member

    Thank for the reply. This actually help answered my other question in the forum. Do you know of a way to back up a logshipping database in the secondary server so I can put the backup file on a backup media for future restore purpose?
  4. gurucb New Member

    One out of the box option that can be tried is why dont you Zip the file before ftp it into secondary server. And I dont think you can take backup of secondary database.

Share This Page