sql express 2005 database has dissapeared after restore.

  • snomadj

    SSCarpal Tunnel

    Points: 4237

    This is live, old I know, but I really need advice.

    So I had a corrupt db.  Backups all screwed.  So we decided to repair if poss.

    set the db to single_user
    backed up.
    dbcc checkdb (db, repair_rebuild) - no joy.
    dbcc checkdb (db, repair_allow_data_loss) - successful, fixed 1 error.
    dbcc checkdb (db) - no more errors 
    set the db to mutli_user

    use db
     & now it's not there. 

    error log shows the db.  
    "setting database option MULTI_USER to ON for database xx"
    then nothing … 

    there are disk issues on here supposedly fixed.  but I see also 
    BEGIN STACK DUMP
    .. encountered error... Invalid access to memory location.

    mdf & ldf not there either.
    help, what to do

  • NorthernSoul

    SSCertifiable

    Points: 6715

    Does it appear in sys.databases?

    Thanks

  • Sue_H

    SSC Guru

    Points: 89699

    snomadj - Wednesday, February 6, 2019 5:21 AM

    This is live, old I know, but I really need advice.

    So I had a corrupt db.  Backups all screwed.  So we decided to repair if poss.

    set the db to single_user
    backed up.
    dbcc checkdb (db, repair_rebuild) - no joy.
    dbcc checkdb (db, repair_allow_data_loss) - successful, fixed 1 error.
    dbcc checkdb (db) - no more errors 
    set the db to mutli_user

    use db
     & now it's not there. 

    error log shows the db.  
    "setting database option MULTI_USER to ON for database xx"
    then nothing … 

    there are disk issues on here supposedly fixed.  but I see also 
    BEGIN STACK DUMP
    .. encountered error... Invalid access to memory location.

    mdf & ldf not there either.
    help, what to do

    If you have had disk issues and are still getting stack dumps, you may want to focus on those issues first and figure out what is wrong with the server or pc where the instance is installed. You can see things "disappear" when you have disk issues.

    Sue

  • snomadj

    SSCarpal Tunnel

    Points: 4237

    Thanks for the replies. 

    So yes, the database was no longer listed in sysdatabases, the mdf & ldf disappeared & the db was - no shit - not accessible.  All the blood drained from my body.  It was the weirdest thing.  I'm the only one on the box and I know I didn't issue a DROP.  So, either it was disk weirdness OR to do with the REPAIR_ALLOW_DATA_LOSS though the cmd had successfully completed and I'd put it in multi_user  before it disappeared.

    The wintel boys told me the disk issues were resolved, though clearly something - disk or memory was / is still an issue. I did the following:
    restart SQL service.
    restore to my backup (I was nervous it wouldn't as it was a backup of a db with some corruption issues)
    redo everything EXACTLY THE SAME
    & it was fine.
    Passed back to Wintel to resolve.

    I can't explain it, which I'm not at all comfortable with - if anyone has any more insight I'd love to hear it though if you've seen this similar occur due to disk issues previously maybe it is just that sue.  I guess what I find extra weird is that the mdf & the log BOTH disappeared along with the meta data in one clean fell swoop.  If I was reading this and someone else was writing it I would be rolling my eyes and suggesting they somehow dropped the db & didn't realise.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714067

    I'd be concerned that someone mucked with storage coincidentally.

    I'd also make sure if you're doing this, you use a script and save it, just to be sure you do the same things. It's easy in crisis to think you've run things or add/remove something.

    What you described shouldn't happen.

  • snomadj

    SSCarpal Tunnel

    Points: 4237

    Thanks Steve.  It is possible.  I was remoted in and comms were being directed through a 3rd party so I didn't have eyes on the h/w or the ear of the onsite engineer.  The original issues were caused by storage and supposedly resolved before I started work. It's an unusual, flaky and unsupported set up with super old s/w.  The windows event logs were full of errors but nothing I could put my finger on as being the cause. 

    Good point about the script. I didn't use one though I had notepadded all my cmds ahead of time but mostly because for unrelated reasons I had to do all this via osql.  In future I will be extra systematic.

    p.s. I'm aware this all makes us sound a bit cowboy but my current client is an unusual one with certain constraints and my remit here is to make their set up work as is.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714067

    I was speculating, and certainly understand the strange circumstances. I like saving scripts of what I've done, and glad you used Notepad. Heplful to reproduce things.

Viewing 7 posts - 1 through 7 (of 7 total)

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