SQL Server Performance

manual way of doing log shipping

Discussion in 'SQL Server 2005 Log Shipping' started by Akthar, Jun 12, 2007.

Page 1 of 2

  1. Akthar New Member

    hi guys,
    after many attemps to do log shipping on my SQL server 2005. I am looking for a manual way of doing it without having to use any domain account to start sql Services.
    like:
    1- Backup the Tlog on the primary server
    2- copy the logs to the secondary server using windows tasks
    3- restore the logs on the secondary server.

    What do you guys think??? please advice



    AKTHAR
    Akthar, Jun 12, 2007
    #2

  2. FrankKalis Moderator

    FrankKalis, Jun 12, 2007
    #2

  3. satya Moderator

    Can also take help of Brad's articlehttp://www.sql-server-performance.com/sql_server_log_shipping.asp in this regard.

    If you are using without a domain account then ensure to use local Administrator account or similar local account (name) between the servers having same password, otherwise the copy will fail.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 12, 2007
    #2

  4. Akthar New Member

    thanks guys. in fact i will be using a windows Task schedule to copy the backup file from one server to the other. Only this task will be using a domain account.<br /><br />Thanks for all the advice. I really appreciate this FORUM as everyone is always trying to help each other.<br />Thank you for creating such a nice and useful community.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />AKTHAR
    Akthar, Jun 12, 2007
    #2

  5. satya Moderator

    Glad to hear you are finding the site useful to resolve the issues, but on that aspect as a pre-cautionary measure I would suggest to go the way I suggested. Say if any changes happened to that account within the O/S then log shipping will have the failure which is nothing but a risk of losing the standby approach.

    I have had similar issues so suggested the better route.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 13, 2007
    #2

  6. Akthar New Member

    thanks for the advice sayta.


    AKTHAR
    Akthar, Jun 13, 2007
    #2

  7. MohammedU New Member

    Most of times I use custom log shipping instead of build in....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
    MohammedU, Jun 13, 2007
    #2

  8. Akthar New Member

    i am having an issue when trying to restore the database. It seems that Sql is using the agent account to restore the database on the secondary server and it cant???



    AKTHAR
    Akthar, Jun 14, 2007
    #2

  9. Akthar New Member

    sorted out
    i have created a SP on the secondary server and create a liked server on the main server and then called the SP in the JOB Step.

    Thanks

    AKTHAR
    Akthar, Jun 14, 2007
    #2

  10. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] That is the reason referred Brad's article in this regard for such custom based log shipping.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, Jun 14, 2007
    #2

  11. Akthar New Member

    Thanks all for your help

    AKTHAR
    Akthar, Jun 14, 2007
    #2

  12. Akthar New Member

    Hi guys,
    i have setup my jobs - to do a manual logship
    BackupLog
    CopyLog to secondary server
    restore log.

    The steps runs successfully but i can find the updated information in my secondary database.
    any help plz


    AKTHAR
    Akthar, Jun 18, 2007
    #2

  13. satya Moderator

    What do you mean not able to find update information on secondary database?
    As it will be in LOADING state for next log restore, have you attempted to make it online looking for such records.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 18, 2007
    #2

  14. Akthar New Member

    i tried to do some changes in the main database, and then run the backup and restore log job several times. When i recover the secondary database, i dont see the change rows!!!

    AKTHAR
    Akthar, Jun 18, 2007
    #2

  15. satya Moderator

    Have you used WITH RECOVERY option during that log restore on secondary database?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 18, 2007
    #2

  16. Akthar New Member

    i have used with norecovery?

    AKTHAR
    Akthar, Jun 18, 2007
    #2

  17. satya Moderator

    Try to restore that log and see how many rows were affected.
    Then how do you know that updated records on secondary database are not available.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 18, 2007
    #2

  18. Akthar New Member

    when i restore the tr logs manualle i can see all the datas!

    AKTHAR
    Akthar, Jun 18, 2007
    #2

  19. satya Moderator

    ?? Are you able to see the relevant data now?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 18, 2007
    #2

  20. MohammedU New Member

    Check the msdb..restorehistory table for your history...
    If your logs are restored successful... then you should see the data...
    You may be inserting the data into a diffent db or server...
    or your log restores are failing...

    Instead of recovering the database you can restore as standby so that you can read the data without recovering the database.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
    MohammedU, Jun 18, 2007
    #2

  21. satya Moderator

    ...also if you can see the log has been restored successfully on the error log of secondary server then it is applied, also you can keep a track of such actions by enabling log for that job.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 19, 2007
    #2

  22. Akthar New Member

    tried standby also,<br />same problem[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />AKTHAR
    Akthar, Jun 19, 2007
    #2

  23. satya Moderator

    If you are scheduling with a job then see whether job has executed successfully, also the duration to complete that job.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 19, 2007
    #2

  24. Akthar New Member

    yes all the jobs are successfully completed

    AKTHAR
    Akthar, Jun 19, 2007
    #2

  25. satya Moderator

    How about the duration of each job?
    Backup log -?
    Copy log - ?
    Restore log -?
    How about service account between the servers?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, Jun 19, 2007
    #2

  26. Deepak Rangarajan New Member

    Hi Akthar,
    Did u check ur copy job was it successful??.....may be it wud have failed and accidentally u might not have viewed it.....check whether that particular tran log is available in both source and destination server........

    Regards
    Deepak
    SQL DBA
    Deepak Rangarajan, Jun 20, 2007
    #2
Page 1 of 2

Share This Page