SQL Server Performance

Transaction log restore(LOG SHIPPING)

Discussion in 'SQL Server 2005 Log Shipping' started by lakshmi.aravind@fidelity.co.in, Oct 18, 2007.

  1. Hi we have a database on sqlserver 2005 on a schedule for transaction log backups is in place already which is happening at interval of 30mins.All these transaction logs are appended to file called 'BKP' on E drive of our server. Now i have to configure logshipping for this database.Normally logshipping process will backup the transaction log,stores it in a shared folder on primary server and then copies the backupfile to another folder on secondary server and then restored ..plz correct me till now if im wrong.Now let us say our normal trans log backup schedule is as follows
    T1 at 9.30am
    T2 at 10.00am
    T3 at 10.30am
    T4 at 11.00 am
    Now i decided to schedule the log shipping transaction log backup interval once in 30mins and start transaction log backup at following intervals
    T11 AT 9.45am
    T22 AT 10.15AM
    T33 at 10.45AM
    T44 AT 11.15AM
    So T11,T22 etc these backups are restored on standby server sequentially.
    Now if Primary database goes down at 11.25am , then do i have to restore the Tlogs T1,T2,T3,T4 and make the Primary database up or i should restore T1,T11,T2,T22,T3,T33,T4,T44 for making my primary database UP..
    Regards
    Arvind L
  2. techbabu303 New Member

    Hi ,
    T11,T22,T33,T44 that should do it.
    I would suggest when u go for log shipping use diffrential backup every four hours and Trans log back up every 30 min , so retore from last diff backup before the failure and then restore each Translog after that to do point in time recovery.
    Regards
    Sat
  3. hi Sat,
    thanks for the jet speed response. But if T11,T22,T33,T44 are restored sequentially,then will there not a discontinuity in logbackups LSNs?
    As i feel when T11 is restored first and T22 is restored next,the last LSN of T11 will not match with first LSN of T22 because there is a intermediate backup going on T2 RIGHT??
    Please clear this also.. during logshipping process the tlog backups are copied in to primary shared folder physically and will remain there as it is and copied to secondary and restored..and subsequent tlogs will also be stored on primary shared folder?..
    so primary shared folder consists of bunch of transaction log backups right..plz clear this concept also..
    Thanks
    Arvind
  4. techbabu303 New Member

    I guess, I assumed the T11,T22,T33,T44 are restored on standy server [:)]
    I would suggest you get Satya or any MVP's understanding on this since Ima confused at this point of time.
    Regards
    Sat
  5. satya Moderator

    To avoid mis-understanding of log backup file numbers I would suggest to use timestamp than using such numbers.
    In your case if the database is offline at 11:30 and in order to recover the db you have to restore from last database backup + all the transaciton log backups (in order of time) and use WITH RECOVERY for last transaciton log backup completely.
    In the log shipping process you would have the relevant log backup files on primary & secondary server, provided you have opted to copy the file from primary to secondary.
  6. Thanks satya for the idea....but my doubt is not still cleared..see basically i wanted to know whether we can restore the transaction logs sequentially on secondary server through the backup schedule of logshipping when already a normal backup schedule is in place at 30mins interval..
    Yes i know we need not follow another backup schedule when we r implementing log shipping bcoz tlog backup is already made by log shipping..but here i wanted to test logshipping process on a database which already has a backup strategy as mentioned on my first post.
    As i said my normal tlogs backups(NOT logshipping generated backups) are getting APPENED to a file.Here i was giving a thought whether i can use the same transaction log backup that are generated (from my normal backup schedule and getting appended to a FILE) for my LOG SHIPPING Process???
    Bcoz from the GUI of sqlserver 2005 log shipping process, i was able to see that trasaction log backup schedule can be DISABLE..so if i disable this backing transaction log job i thought i can reuse the same transaction log backups from my normal backup schedule for the log shipping process and then copy the same logs to secondary and restored....bt im not very confident abt this idea..can u suggest some ideas abt this too?
  7. dba247 New Member

    Well if you are not confident then the best thing would be to TEST it.
    create a small database in the primary (called TEST maybe?) and test it out... that's the best way to learn :)
    Now one of your questions was --
    "Bcoz from the GUI of sqlserver 2005 log shipping process, i was able to see that trasaction log backup schedule can be DISABLE..so if i disable this backing transaction log job i thought i can reuse the same transaction log backups from my normal backup schedule for the log shipping process and then copy the same logs to secondary and restored....bt im not very confident abt this idea..can u suggest some ideas abt this too?"
    The answer is YES - you can use the same transaction log from the regular/normal backup schedule for the log shipping process and copy+restore it on the secondary (this is what we do at our end -- 'restore with standby' option on the standby using logs from regular backups -- since I cannot set up "log shipping" due to some network/security constraints ).

    About your first question -- as long as you have all the LSNs sequence from the normal backup
    in the backup file that's being appended then I believe you can use those to recover the standby (i.e.
    you probably need only T1,T2,T3,T4 and don't need T11,T22,T33,T44).
    I am going to test this anyway on my end and let you know if that's not the case.
  8. satya Moderator

    The series of transaction log backups to restore must be followed provided you have performed them using SQL native statements and not used with third party tools.
    In this case yes you have to go through the order to achieve the same.

Share This Page