Results 1 to 6 of 6
  1. #1
    Join Date
    May 2015
    Posts
    1

    Answered: How to view content damaged ms sql server database?

    Hi guys

    I got a huge problem. One of my employes deleted a MSSQL Database instead of detach. Now i tried to recover the .mdf and .ldf file but when i trie to attach it it says. "File is not a primary database file"

    Anybody got an Idea how to fix this? It's very urgent.

  2. Best Answer
    Posted by marfanils055

    "Right click on the database name.

    Select Tasks -> Shrink -> Database

    Then click OK!

    I usually open the Windows Explorer directory containing the database files so I can immediately see the effect.

    I was actually quite surprised this worked! Normally I've used DBCC before, but I just tried that and it didn't shrink anything so I tried the GUI (2005) and it worked great - freeing up 17Gb in 10 seconds

    Edit: In Full recovery mode this might not work, so you have to either back up the log first, or change to Simple recovery, then shrink the file.

    ps: Use MDF Viewer Tool from http://www.mdf.viewertool.com/"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First order of business, do you have good backups? Those are your first line of defense against an accidentally damaged/deleted database.

    If you have to recover from the drop, things get ugly very fast.
    1. What version of SQL Server are you running?
    2. What patches are installed?
    3. Are there only two files (*.mdf and *.ldf) for this database?
    4. SQL Server and what services/programs are running on the Windows Server?
    5. Did you stop all user access immediately after discovering the dropped database?

    After you answer these questions, there will be more questions. Depending on the urgency and cost to the business, you may want to engage a professional to help you recover as much data as possible instead of trying to recover it yourself.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    May 2015
    Posts
    1
    Provided Answers: 1

    Solution for this problem of dbase

    Right click on the database name.

    Select Tasks -> Shrink -> Database

    Then click OK!

    I usually open the Windows Explorer directory containing the database files so I can immediately see the effect.

    I was actually quite surprised this worked! Normally I've used DBCC before, but I just tried that and it didn't shrink anything so I tried the GUI (2005) and it worked great - freeing up 17Gb in 10 seconds

    Edit: In Full recovery mode this might not work, so you have to either back up the log first, or change to Simple recovery, then shrink the file.

    ps: Use MDF Viewer Tool from http://www.mdf.viewertool.com/

  5. #4
    Join Date
    Jun 2011
    Posts
    31
    Provided Answers: 1
    This error normally occurs if the file header of the master database gets corrupted or damaged. SP_attach_db' only attach database that is properly detach by sp_detach_db. You can use 'CREATE DATABASE' with 'FOR ATTACH' clause. For more details check this blog http://www.stellarsqldatabaserecover...-error5171.php

  6. #5
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Was there any mirroring in place or replication?
    Is there an ad-hoc backup on disk available, check backup history of a single database with this script - http://www.sqlserver-dba.com/2012/06...-database.html

  7. #6
    Join Date
    Jun 2011
    Posts
    31
    Provided Answers: 1
    This error normally occurs if the file header of the master database gets corrupted or damaged. SP_attach_db' only attach database that is properly detach by sp_detach_db. You can use 'CREATE DATABASE' with 'FOR ATTACH' clause. For more details check this blog http://www.stellarsqldatabaserecover...-error5171.php

Tags for this Thread

Posting Permissions

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