none
Question about the initial backup for log shipping - SQL 2008

    Question

  • Hi All, I'm in the process of setting up log shipping for several SQL 2008 DB's.  Everything works fine using a small test DB, but for the next step, I need to configure Log shipping for a 100+GB DB that will be backed up and shipped across a 35Mbps WAN link.  If I select to backup that DB using the Log Shipping Wizard and it takes 8+ hours, do I need to be concerned about any changes that have occurred since the start of the backup or will it automatically know which transaction logs to apply after the backup completes?  If you've setup something similar, are there any gotchas or best practices I should be aware of when setting up log shipping for a large DB across a WAN link?  *This DB gets moderate use and we don't need an aggressive RPO so I'll probably opt for a 30 minute schedule for Log Shipping jobs.

    Thanks!   

    Wednesday, July 02, 2014 6:51 PM

Answers

  • Hi,

    You should not use backup by log shipping GUI( I have seen issues with that) but should manually backup and restore it on destination. After you took full backup and suppose it took few hours to copy and restore it in standby on secondary server you dont need to worry because subsequent log backup would include all changes happened after full backup.And these log backup would be applied to secondary after you configure Logshipping successfully

    Its also not necessary to restore  log backup in Logshipping( after you restore full backup).  But you should make sure that if you take multiple log backups after full backup they all should be applied to secondary in sequence on secondary to successfully configure log shipping.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Wednesday, July 02, 2014 7:02 PM

All replies

  • Hi,

    You should not use backup by log shipping GUI( I have seen issues with that) but should manually backup and restore it on destination. After you took full backup and suppose it took few hours to copy and restore it in standby on secondary server you dont need to worry because subsequent log backup would include all changes happened after full backup.And these log backup would be applied to secondary after you configure Logshipping successfully

    Its also not necessary to restore  log backup in Logshipping( after you restore full backup).  But you should make sure that if you take multiple log backups after full backup they all should be applied to secondary in sequence on secondary to successfully configure log shipping.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Wednesday, July 02, 2014 7:02 PM
  • Just restore the database from backup and restore few tlog backups to keep your DB sync narrow and configure the logshipping, dont intiate the complete fullbackup restore via logshipping configuration, it will take ages for large databases.


    Raju Rasagounder Sr MSSQL DBA

    Wednesday, July 02, 2014 11:40 PM
  • Thanks guys... so I did the manual backup/restore w/ No Recovery and then setup log shipping.  All seemed to proceed without error.  However, I'm not sure how to tell if it's truly working properly.  When I run a log shipping status report, I'm seeing a Red Alert, but when I look at the LSBackup, LSCopy and LSRestore job history, everything is green and happy? 

    Another question, after log shipping has been setup, can you change between No Recovery and Standby mode or do you need to start all over?
    Tuesday, July 08, 2014 6:07 PM

  • Another question, after log shipping has been setup, can you change between No Recovery and Standby mode or do you need to start all over?
    You can do that without starting from scratch. You should look at history of each job and read it carefully you will find information why jobs are failing.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Tuesday, July 08, 2014 6:54 PM
  • You can change the setting of Log shipping without breaking the exisitng..

    You can change the settings in 2 different ways.

    1st Method.
    1st step == Execute this command on your primary server for changing retention value of backup files on primary server
    exec master.dbo.sp_change_log_shipping_secondary_database @database =
    2nd step == Execute this command on your secondary server for changing retention value of copied backup files on secondary server
    exec master.dbo.sp_change_log_shipping_secondary_primary
    @primary_server = ,
    @primary_database = ,
    @file_retention_period =

    2nd Method.
    Right click database for which value needs to be changed then select tasks and ship Transaction logs.
    Log shipping configuration window pops up.
    For deleting files on primary server, click the backup setting and modify the value under delete files older than heading.
    For deleting files on secondary server, click secondary server ellipsis button for which value needs to be changed here modify the value under copy files.


    • Edited by Chetan_DBA Wednesday, July 09, 2014 3:05 PM
    Wednesday, July 09, 2014 3:03 PM
  • Thanks Guys, I think I figured out what's happening.  The restore job is skipping all the log files.  I believe this is occurring because there is another job that was previously setup that backs up the same DB transaction logs and it's conflicting with the log shipping.  So from what I can gather, I'll need to stop the other log back up job, perform a new full backup and then restore to DR with No Recovery and setup log shipping from scratch?  
    • Proposed as answer by Chetan_DBA Thursday, July 10, 2014 9:51 AM
    Wednesday, July 09, 2014 9:40 PM
  • That would be proper way...

    Chetan

    Thursday, July 10, 2014 9:50 AM