Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2006
    Posts
    27

    Unanswered: Restore tranlog backup file

    I have 2 questions, and I appreciate if somebody can help me to figure out the good way to do. Thanks a lot.

    1/. If I want to restore tranlog backup file from linked server to SQL Server 2000, Is the database online or offline during that time (since I want to make sure db online for users, but not offline)

    2/. Can I restore tranlog backup file from SQL Server 2000 to SQL Server 2005 database?

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    1) Your question is not completely clear, you want to restore a tranlog from "linked server to SQL Server 2000"???
    But in general to restore a tranlog you will first have to restore a previous FULL BACKUP and all following tranlog backups until the tranlog you wish to restore last. During this whole process the db is in recovery mode, this means it is not possible to connect to this database.

    2) Sadly I cannot answer this question for you because I don't know

  3. #3
    Join Date
    Oct 2006
    Posts
    27
    To the 1st question, I mean I want to restore the last tranlog backup file from Linked Server to SQL Server2000 (that I am using now) since I did backup from Linked Server and want to restore it in the current server.

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Could you explain what you mean by Linked Server. To my knowledge that's just a link between two SQL instances and has nothing to do with log backups.

  5. #5
    Join Date
    Oct 2006
    Posts
    27
    Oh, Linked server here I just mean another server (named Enterprise Server)linked to the SQL Server 2000 that I am using now. And I use the tranlog backup file in The Enterprise server to restore in SQL Server 2000. Actually, The Enterprise Server (linked server) does the whole database backup and I want to restore the tranlog backup file in SQL Server 2000.
    When you open 'Enterprise Manager' in SQL Server 2000,
    click 'Security' in the tree of the sql instance, you will see 'Linked Servers' located.
    Thanks for concern

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool backup error run as a job

    BACKUP DATABASE [master] TO DISK = N'\\dca-05\sqldumps\Daily\DCA-SIEBDB_master_db_complete.BAK' WITH INIT , NOUNLOAD , NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT


    When this is run as a job I get errors. If i run this from the anlyzer it run fine.

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Agent jobs run under the Agent login privileges and not yours. QA runs under your credentials. If the agent does not have the proper access on both machines, it fails.

    Either make the agent account a local admin on both machines (if same domain), a domain admin, or build a trust relationship if different domains.

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by tomh53
    Agent jobs run under the Agent login privileges and not yours. QA runs under your credentials. If
    Not to undermine the point you're making but the access to the file share goes under the credentials of SQL Server, not the credentials QA is running under.


    But we're diverting from the question from jennyphb, this should be a seperate thread!

    @jennyphb: I stand with my first answer that in order to restore a tx-log you will first have to restore a full backup (with NO RECOVERY) and subsequently the tx-log. During this time the db will be inaccessible to anyone.

  9. #9
    Join Date
    Oct 2006
    Posts
    27
    Is there anyone who can help me to answer my 2nd question:

    "Can I restore tranlog backup file from sql server 2000 to sql server 2005 database? (the same db but just different server since I want to upgrade to sql server 2005)."

    Thank you very much.

  10. #10
    Join Date
    Oct 2006
    Posts
    27
    Thank you Lexiflex for your 1st answer.

  11. #11
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Second question

    Sure, This can be done. You should have the same rights and credential as on the other machine.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jennyphb
    Is there anyone who can help me to answer my 2nd question:

    "Can I restore tranlog backup file from sql server 2000 to sql server 2005 database? (the same db but just different server since I want to upgrade to sql server 2005)."

    Thank you very much.
    you can restore a 2k database backup to a 2k5 server but I am not sure and i doubt this would work. I would test it on a database that is not in production.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool backups and restores

    I've done this before only with 2000 many times. I haven't done ti with a 2000 backup to restore on 2005. Simple. Just use the DTS utility to bring everything over to setup all the protocals in 2005. Then just load the data.

    G That's me

  14. #14
    Join Date
    Oct 2006
    Posts
    27
    what do you mean "everything"?
    and how do you load the data? detach/attach? ...
    thanks

  15. #15
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Everything is everything "what will be will be"

    First of all, calm down. I can see by the number of post this can be a little overwelming. It happen to me to like most people in this industry. Please see attachment screen shoots

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •