Post reply

log shipping from SQL 2000 to SQL 2008 in order to keep the tow databases in sync

  • August 31, 2011 at 7:47 am

    #390313

    HI All

    i have to keep two databases in sync one in prod sql 2000 and one in dev sql 2008, need steps or scripts to do log shipping from SQL 2000 to SQL 2008 in order to keep the tow databases in sync.

    Thanks for stopping by

  • August 31, 2011 at 12:50 pm

    #1375642

    why would you want a dev database that is in restoring state?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 31, 2011 at 12:54 pm

    #1375646

    You can log ship 2000 - 2008, but the secondary database will be in the restoring state and will be completely inaccessible.

    Perhaps replication is more what you need (but replicating prod -> dev could create all sorts of security nightmares)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 31, 2011 at 1:33 pm

    #1375681

    Hi, perry

    my bad we are moving the sql 2000 db i,e production to sql 2008 dev so, we are tesing

    Thank you

  • August 31, 2011 at 1:51 pm

    #1375688

    Ok so if youre moving a copy to dev why are you log shipping it? The log shipped database will not be in a state that will allow access to the database. The most log shipping allows in a supported configuration is read only. Why not just backup and restore to the dev server

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 31, 2011 at 1:56 pm

    #1375690

    perry,

    we are testing eventually we want to move to 2008 and it can be read only mode i guess right wile applying logs

  • August 31, 2011 at 2:09 pm

    #1375699

    Ok but if you log ship between 2000 and 2008 the database can only be in restoring mode!!!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 31, 2011 at 2:11 pm

    #1375701

    perry,

    that's fine if it is in restoration mode, but i want to know step by step

  • August 31, 2011 at 2:18 pm

    #1375707

    What edition is your SQL 2000 instance?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 31, 2011 at 2:20 pm

    #1375709

    perry,

    Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  • August 31, 2011 at 2:33 pm

    #1375714

    Sql server 2000 standard does not support log shipping!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 31, 2011 at 2:45 pm

    #1375720

    perry,

    but there are some scripts i guess i saw some people doing it

  • August 31, 2011 at 3:33 pm

    #1375742

    harita (8/31/2011)

    perry,

    but there are some scripts i guess i saw some people doing it

    Log Shipping consist of three operations:

    1- A transaction log backup job (on the primary)

    2- A copy job (copies the transaction log backups from the primary to the standby Server

    3- The restore job on the secondary.

    You can try to create your own jobs (I will not recommend it). Why do you want to do this?



    My blog

  • August 31, 2011 at 3:36 pm

    #1375744

    You may want to take a look at this[/url]. I have no idea if it will work in your case.



    My blog

  • September 1, 2011 at 2:33 am

    #1375831

    Harita

    What is the editon of SQL Server you are using?

    Irrespective of edition you can perform the log shipping using script method. Bear in mind you cannot perform any FAILBACK method from secondary server to primary server, due to the version differences.

    Further if this is test purpose only for your upgrade practice, make sure you have scripted out logins, linked server and DTS packages (again migration or rewrite them in SSIS) that will make a way for better upgrade process in the future.

    -Satya SK Jayanty
    SQL Server MVP (Follow me @sqlmaster)
    Author of SQL Server 2008 R2 Administration CookBook
    SQL Server Knowledge Sharing network

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply