Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    2

    Question Unanswered: Restore MSSQL Database, lost LDF-File

    Hi.
    I've following problem:
    - Customer played with his logical drives in a CPQ smart array
    - He played too much: he lost the drive with the LDF-File
    - Now the SQL-Server is in a kind of wired mode; you can
    start and stop it, but you cannot connect to (neiter 'sa' nor
    any other user available before)
    In fact the mode is not wired: what shall he do other when
    most important file is missing...
    - The problem for me now is:

    > How can I get the Enterprise Manager or Query Analyzer
    up and running again to start a restore?

    PS: The restore is to be done from a tape-library running
    Veritas Backup Exec + SQL-Agent

    PPS: Error-log says (what I already know):
    2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:\DEVLOG1\DEVLOG1.ldf' may be incorrect.

    Thanks in advance for assistance.

    Regards,
    Carsten Loeffler
    Last edited by carsten.loeffle; 10-04-05 at 12:43.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    did you try to connect with osql?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Sounds like someone played with the default database of sa and BUILTIN\ADMINISTRATORS. That's bad. You can see if any of the local administrators can connect to the SQL Server with WndowsNT authentication, but it sounds like you have already tried that.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If nobody can connect to SQL Server, maybe you want to run rebuildm, then restore the last good master database and other databases as needed.

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

  5. #5
    Join Date
    May 2005
    Posts
    2

    Anwers to Reply

    Hi Everyone,
    thanks for the assistance.

    Brett:
    No, I didnt try it with OSQL. But thats a good idea. Any hints so far
    before I go and investigate myself too much??

    MCrowley:
    Yip. You're right and I didnt mention it. Yes - I tried it with "sa" and
    I tried it with all the others I knew that they were there.
    The thing that confuses me: master-DB is still there and running
    and didnt get touched...

    Tomh:
    Mmmh. Do you think I have to do it anyway. I think that master-DB
    is up and running.

    Regards,
    Carsten Loeffler

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by carsten.loeffle
    The thing that confuses me: master-DB is still there and running
    and didnt get touched...
    When you log into SQL Server, you always go to your default database first thing. If it is not there, then you get the error "Can not open default database". Has the same thing happened to BUITLIN\ADMINISTRATORS, or can a local Windows admin log in with Windows Authentication?

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by carsten.loeffle
    Hi.
    I've following problem:
    <snip>
    - Now the SQL-Server is in a kind of wired mode; you can
    start and stop it, but you cannot connect to (neiter 'sa' nor
    any other user available before)
    </snip>
    Since we know MCrowley is right, and if the statement above is true, then **all** users were defaulted to the database where the ldf is missing.

    If I'm reading the quoted part right, not a single user can log in. If that is the case, then master will have to be restored. And since you have to be able to log in to rebuild master, rebuildm will be needed to get back to a place where a system admin can login. So maybe rebuildm, restore database where .ldf is missing, them restore master.

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

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by MCrowley
    And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.
    I am gonna bet all the whisky lying in my almirah it will be possible to login using BUILTIN\ADMINISTRATORS. It just seems that the guy is not aware of the account.

    Suggestion : Try logging in with the radio button set to Windows Authentictation while you are logged in with a Windows System Administrator account ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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