none
ssd died with wife's data on it only able to salvage ,mdf file - Save a man's life ... how do I restore / attach / recreate log file - no permissions on new drive

    Question

  •          My wife asked me if I could create a VB program for her - load her data onto SQL Server to test her for some medical exams. Since I have been "playing" with both Visual Studio and SQL for over 10 years, I said sure. Copied her Excel file, transferred the data to SQL and started "playing".

           Play time ended abruptly the other day when my Solid State Drive died. I was only able to salvage the .MDF off the disk. Lost the log file and lost the Excel file. Yes, for all of my databases I had regularly scheduled backups to another disk. I hadn't set that up for hers yet, thinking that the data was available on another computer altogether.

           No big deal, right? Just get the original Excel file back from the wife("Offsite backup") and reload the data from Excel. Problem ... she deleted the file 2 months ago thinking I had the data, and there is no recovery as the sectors where that data was located on her hard disk have since been overwritten.

          As to my situation: I told here that things were "going fine" and that I just had a couple more things to recover and we would be back up in business. Thinking that Dangling from a bar over a tank of great whites with a freshly cut hand might be preferable to telling her that I just fried over 300 hours of her hard to come by, time, by assuming the data was safe.

           From what I can tell, the .MDF file is intact. I have nothing else, and that was attached to a database on what was essentially another computer. Yes, I "Assumed" I had good backups. Yes, I have learned my lesson. Please save a man's life and help me out.

    Friday, June 13, 2014 12:05 PM

All replies

  • From what I can tell, the .MDF file is intact.

    If the MDF file is intact, then you can attach it even without a log file; see http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

    Just ensure the MDF is located in a Folder where SQL Server have full permissions for; as it's best in the DATA subfolder of the SQL Server installation


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 13, 2014 12:17 PM
  • First, thanks for your prompt reply.

    I tried these two sets of commands:

    CREATE DATABASE Q_And_A ON
    (FILENAME = N'E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf')
    FOR ATTACH_REBUILD_LOG
    GO

    And this set of commands as well:

    CREATE DATABASE Q_And_A ON
    ( FILENAME = N'E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf')
    FOR ATTACH
    GO

        Both of these gave me this error:

    File activation failure. The physical file name "E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A_log.ldf" may be incorrect.

    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'Q_And_A'. CREATE DATABASE is aborted.

         The database had been closed properly, with no outstanding transactions / users attached, but that was on a completely different operating system / hard drive as everything had to be reloaded, the operating system, SQL Server .... I also checked the properties on the .mdf file, and they are not “Read Only”. Any other ideas?

    Friday, June 13, 2014 12:49 PM
  • Try

    EXEC sp_attach_single_file_db @dbname='Q_And_A', @physname=N'E:\Mikes\128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf'

    GO

    Friday, June 13, 2014 12:58 PM
  • Tried:

    EXEC sp_attach_single_file_db @dbname='Q_And_A', @physname=N'E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf'

    GO

    Got:

    File activation failure. The physical file name "E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A_log.ldf" may be incorrect.

    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'Q_And_A'. CREATE DATABASE is aborted.

    Is there anything else you can think of?

    Friday, June 13, 2014 1:47 PM
  • HI Alethia,

    Can you please follwo this article by Paul I hope this would help you expecially the section Re-attching the suspect database. read whole link carefully and slowly

    http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/?bcsi_scan_F872AB84FAA8A40E=0


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 13, 2014 2:00 PM
  •                    I had my disk die with the only copy of my wife’s medical “Question and Answer” (Q_And_A)  database on it. She thought it was safe and sound on my computer, and I thought we had an “Off site” backup of the data on hers and hadn’t set up backup for it.

    1.)   The bad things are, only the .mdf file was intact.

    2.)   All of the permissions based on the old copy of Windows 7 and SQL 2005 or 2008 ( I am not sure which the .mdf was created in.) have gone the way of the dodo.

           Bought a new disk. Reloaded windows 7 64 bit. Loaded SQL 2014 (Thought now was a good time to upgrade.) Problem … so far I have not been able to attach and recreate the log file …

              Just to finish up the data I have, the database was closed properly and is NOT in a read only state according to file properties. That said, I still don’t have a solution.

     So far I tried these three sets of commands:

    CREATE DATABASE Q_And_A ON
    (FILENAME = N'E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf')
    FOR ATTACH_REBUILD_LOG
    GO

    And this set of commands:

    CREATE DATABASE Q_And_A ON
    ( FILENAME = N'E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf')
    FOR ATTACH
    GO

    EXEC sp_attach_single_file_db @dbname='Q_And_A', @physname=N'E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A.mdf'

    GO

             So far this is the computer’s reply to everything I have thrown at it. I haven’t told my wife that 600 hours of her work is inaccessible, and I would really rather not do so. Anything anyone can offer would be great.

    File activation failure. The physical file name "E:\Mikes 128GB\SQL2014\MSSQL12.SQL2014\MSSQL\DATA\Q_And_A_log.ldf" may be incorrect.

    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'Q_And_A'. CREATE DATABASE is aborted.

              Is there anything else anyone out there can offer as help? My wife has often said that she doesn’t believe in divorce. But murder she will consider. If she loses 600 hours of her data this could be my last post. I am joking … I think … That said, any help is extremely welcome. Thanks in advance.

    Saturday, June 14, 2014 5:37 AM
  • Alethia,

    Why multiple posts

    Duplicate post for same issue already raised in Data Access forum

    Did you followed the link posted by me in above thread did you got the same error ?. SQL server can only recover data if it is able to rollback and roll forward all transactions when recovery starts but in your case it is not able to. So unfortunately from where I see things are very difficult for you do took backup, any backup of your databases if not I cannot be of much help.

    Please dont reply to this thread reply to old one.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Saturday, June 14, 2014 9:49 AM
  • Hello,

    Obviously you're in a bad spot and the worst case is that all of the data is lost. Since none of the other methods seem to be working for you, I will link you to an article (no sense in re-writing what Paul has written) with steps that can be attempted. Please note that because of the error messages received and the drive failing, there is no guarantee that the data in the file is any good. I also want to note that this method should not be used outside of the last ditch effort (as you're in now) as it can cause even more problems. 

    http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/

    A few other notes:

    1. If the file is damaged by the drive failure, there is a chance the database won't mount at all.
    2. If the boot page and file header pages are good, going to a different version should be avoided as there will be changes made to the database which could error if the file is damaged somewhere else. This would leave you with a broken file permanently.
    3. If the database does mount, there is no guarantee that the data isn't corrupt somewhere else. DBCC CHECKDB is your friend.


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by Uri DimantMVP Sunday, June 15, 2014 5:17 AM
    Saturday, June 14, 2014 6:21 PM
  • There are two possible solutions based on the situation:

    1. First of all, you can use the SQL Server build-in command DBCC CheckDB and CheckTable to correct the errors. See http://technet.microsoft.com/en-us/library/ms176064.aspx and http://technet.microsoft.com/en-us/library/ms174338(v=sql.110).aspx. This is the recommended solution which you should try first.

    2. If the corruption is severe, then you may try some third party tools to scan and recover the data from your corrupted MDF and NDF database files. Below is a list of SQL recovery tools:

    http://www.datanumen.com/sql-recovery/compare.htm

    The list seems to rather comprehensive that including nearly all the popular tools in the market.

    Hope this will help. Good luck!

    Saturday, June 14, 2014 11:37 PM
  • First, Thanks to everyone as it appears we are making progress. I:

    1.) Followed the article by Paul (Wonderful examples and Step by Step Instructions by the way.) as closely as is possible.

    2.) After finishing all the steps, I entered this command afterwards as requested:

    SELECT

    DATABASEPROPERTYEX(N'Q_And_A',N'STATUS')ASN'Status';

    GO

    3.) Status = ONLINE

         However when I try to expand the Database to see the tables, ... I get: "The database Q_And_A is not accessible. (ObjectExplorer)"


    Sunday, June 15, 2014 5:26 PM
  • First, Thanks to everyone as it appears we are making progress. Sorry about having two different areas related to this post.

    1.) Followed the article by Paul on Creating, Detaching, ...  (Wonderful examples and Step by Step Instructions by the way.) as closely as is possible.

    2.) After finishing all the steps, I entered this command afterwards as requested:

    SELECT

    DATABASEPROPERTYEX(N'Q_And_A',N'STATUS')ASN'Status';

    GO

    3.) Status = ONLINE

         This is wonderful progress. Again, thank you. 

    However when I try to expand the Q_And_A Database to see the tables, ... I get: "The database Q_And_A is not accessible. (ObjectExplorer)"

    It still shows as a single use database in ObjectExplorer Databases, but I can't get to what's inside.

    Thanks for sticking with me on this.

    Sunday, June 15, 2014 5:30 PM
  • Can you run below in the database.Please post the result here

    DBCC CHECKDB(DB_NAME) WITH NO_INFOMSGS, ALL_ERRORMSGS


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Sunday, June 15, 2014 6:19 PM
  • Please DO NOT doublepost. Although your situation might be important to you, does not means that people want to spend their time giving answers that might have already been given in the parallel forum. So please be polite and respectful and only post to one forum with the same question.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Sunday, June 15, 2014 8:02 PM
    Moderator
  •        Thanks again for all the help.

            I apologize for not understanding how things worked. I won't double post again.

            I ran the command above and it executed without errors.

            While it does appear that I "have access" to the "database", none of the tables, views, diagrams, ... are visible, and thus none of the data is accessible.

            Where do I go from here? Is there anything else I can try or do I increase my life insurance or buy a ticket to Brazil? Again, I do appreciate all of the efforts so far.

           In case we would need to start over and take a different route, I do have "Backups" of the original "screwed up" .mdf. (Yes I know... NOW he has backups.)

    Monday, June 16, 2014 3:26 PM
  • Seems your database is fine. You are not logging with account with proper privileges. What rights your login has can you try logging with SA ? or any other login having admin rights in SQL Server


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Monday, June 16, 2014 3:33 PM
  • You mentioned that you are now on SQL 2014, which introduces an upgrade into the process of bringing the database online.  I'm not sure if that can be a complicating factor here.

    As Sean noted above:

    2. If the boot page and file header pages are good, going to a different version should be avoided as there will be changes made to the database which could error if the file is damaged somewhere else. This would leave you with a broken file permanently.

    So I would repeat the process with the version of SQL Server that the database was originally running on.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, June 16, 2014 3:34 PM
  • I can "try" this. One of the issues is that I had 2005, 2008 and 2012 loaded and I am not sure which the database was created in. I guess I start at the beginning, reload 2005, check to see if I have access, if not uninstall and try 2008, ...
    Monday, June 16, 2014 4:09 PM
  • If anyone answered your question, feel free to mark it as answered or come back with your solution to the forums and afterwards mark this as the answer how you fixed it.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Friday, June 20, 2014 4:16 PM
    Moderator
  • SOrry folks, a portion of my mothers roof blew off in a storm. Ended up flying back to Illinois to help. I'm back now. Ok, loaded SQL 2005 and 2008. At first got message that a user in SQL 2014 was attached. Solved that. Now, when trying to attach through 2005 or 2008 I get this message.

    "Attach database failed for SQL2008Express …

    The Database ‘Q_And_A’ cannot be opened because it is version 782. This server supports version 655 and earlier. A downgrade path is not supported. Could not open new database ‘Q_And_A’ CREATE DATABASE is aborted …".

    Sorry for the delay in getting back.

    Sunday, July 06, 2014 3:10 PM
  • Hello,

    Looks like you already updated to 2014 as the physical database version of 782 = SQL Server 2014. You'll have to install and attempt to attach it to that version through the same methods outlined in the post I made earlier.


    Sean Gallardy | Blog

    MCM 2008

    MCSM:Data Platform Charter Member

    • Proposed as answer by Olaf HelperMVP Tuesday, July 08, 2014 11:25 AM
    Sunday, July 06, 2014 4:27 PM