none
Database deleted but still seems to be working - Puzzled!

    Question

  • SQL Server 2012

    This morning, I accidentally deleted a database in SSMS by right clicking on the database name and then choosing "delete".

    However, the application that uses this database still seems to be working so I'm quite puzzled.

    I can see two database engines:
    * SQL Server 11.0.3128

    * SQLEXPRESS (SQL Server 10.0.5500)

    The database I deleted was in SQL Server 11.0.3128

    I have shut down and restarted my machine.

    I'm testing a product and I did not create a backup (silly me!)

    Q1: Is there any way that I can recover the database that I accidentally deleted?

    Q2: How come the application is still running?

    Any clues  anyone?

    Thanks

    Ken


    Ken Evans

    Thursday, September 26, 2013 8:15 PM

Answers

  • Q1: if you have a backup of the database, you can restore that to that state. You have to find the latest backup file.

    http://technet.microsoft.com/en-us/library/ms177429.aspx

    Q2: Only possibility: It's ot using the database live but only working with cashed data. Or it's not really using the database at all for the parts that ypu tried.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Thursday, September 26, 2013 8:27 PM
    Thursday, September 26, 2013 8:22 PM
  • Sorry Andreas, I just didn't see your answer!

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Marked as answer by Ken Evans Thursday, September 26, 2013 9:03 PM
    Thursday, September 26, 2013 8:30 PM
  • Sorry for overlooking the "no backup" part.

    as for the mdf file, it must be an express edition- probably even user instance mode

    check the connection sting for this:

    Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;

    In that case you could copy the mdf to later attach it to your instance

    You should first read about this concept here, though: http://msdn.microsoft.com/en-us/library/ms254504.aspx

    Then you know, why I don't recomment to move it right away. Make sure that the application uses the new connection string after you moved it to the "real" instance that you administer.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Thursday, September 26, 2013 9:44 PM
    • Edited by Andreas.WolterMVP Thursday, September 26, 2013 10:08 PM typo
    Thursday, September 26, 2013 9:21 PM
  • ...

    My plan is that at some time in the future I backup the database and restore it to a hosting service.
    It seems that this won't work with an .mdf file.

    So, is it possible to recreate the master database from the mdf file?

    ...

    Well, once you have the mdf attached to the SQL Server Instance that you are actively monitoring/administering, you can take a valid backup from the database and there you go.

    It might even work with just the mdf, if they ust attach it to their instance. Won't be a problem in my eyes, since you can only copy/move the mdf when the instance using it has taken it offline (and checkpointed all data). I'd recommend Backup&Restore though.

    Don't mix it up with the "master" database. This is the system database, and I don't think you really want to deploy that one. :)


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Friday, September 27, 2013 6:39 AM
    Thursday, September 26, 2013 10:08 PM
  • So is it a name?

    Is it a name?


    Ken Evans

    :-D

    noooo, it's not a "name", it's just the "identifier"

    just kidding

    Yeah, it is. But now that you have attached it, it should be easy to just rename it.

    I suppose the mdf itself has a shorter and more meaningful name. Maybe use that one, so it's in sync


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Friday, September 27, 2013 9:09 AM
    Friday, September 27, 2013 8:54 AM

All replies

  • Q1: if you have a backup of the database, you can restore that to that state. You have to find the latest backup file.

    http://technet.microsoft.com/en-us/library/ms177429.aspx

    Q2: Only possibility: It's ot using the database live but only working with cashed data. Or it's not really using the database at all for the parts that ypu tried.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Thursday, September 26, 2013 8:27 PM
    Thursday, September 26, 2013 8:22 PM
  • A1: If you have backup, you can use it. 

    more info: SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

    A2: Check your application that connect with which connection string? May be, just may be you still have a chance and the real database are hosted by another instance.

    Another possibility is that you do not have permission to see that database right now.


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Thursday, September 26, 2013 8:28 PM
  • Sorry Andreas, I just didn't see your answer!

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Marked as answer by Ken Evans Thursday, September 26, 2013 9:03 PM
    Thursday, September 26, 2013 8:30 PM
  • Andreas,

    Thanks for your ultra quick answer.

    As I mentioned, I did not make a backup.  

    Your answer is not the answer that I would have liked, but it's what I thought was probably the case.

    From the limited information I was able to glean in the few hours that I have been testing the product, it looks as though it is has a lot of code outside the 190 table database so maybe the bit of the product that is still working is storing its data in an XML file or similar.

    Thanks again for your quick response.

    Ken


    Ken Evans

    Thursday, September 26, 2013 8:35 PM
  • Hi Saeid,

    What an ace you are!

    I checked the connection string and found an .mdf file several layers deep within the Program Files(x86) folder.

    This puzzles me.

    The product installer asked me for database information and the installer then generated a database that appeared within SSMS that had the name that I had chosen during the installation sequence.

    Naturally, I assumed that this was the database that the product was using. - Silly me again!

    Any idea why an application would generate and install two databases?

    And how can I get at the .mdf file using SSMS?

    Thanks


    Ken Evans


    • Edited by Ken Evans Thursday, September 26, 2013 9:16 PM typo
    Thursday, September 26, 2013 9:15 PM
  • Sorry for overlooking the "no backup" part.

    as for the mdf file, it must be an express edition- probably even user instance mode

    check the connection sting for this:

    Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;

    In that case you could copy the mdf to later attach it to your instance

    You should first read about this concept here, though: http://msdn.microsoft.com/en-us/library/ms254504.aspx

    Then you know, why I don't recomment to move it right away. Make sure that the application uses the new connection string after you moved it to the "real" instance that you administer.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Thursday, September 26, 2013 9:44 PM
    • Edited by Andreas.WolterMVP Thursday, September 26, 2013 10:08 PM typo
    Thursday, September 26, 2013 9:21 PM
  • Absolutely agree with Andreas, you can attach it!

    Just my two Cents;

    Please check if you can move that .mdf file? If you cannot do this, it's probably there is another instance on your machine that host this .mdf file.


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Thursday, September 26, 2013 9:30 PM
  • Thanks for the link. That article partly explains what's going on.

    The situation is:
    I'm using the application & its database using localhost on my own machine so I have full administrative privileges.

    Nobody else has access.

    My plan is that at some time in the future I backup the database and restore it to a hosting service.
    It seems that this won't work with an .mdf file.

    So, is it possible to recreate the master database from the mdf file?

     


    Ken Evans


    • Edited by Ken Evans Thursday, September 26, 2013 9:44 PM typo
    Thursday, September 26, 2013 9:44 PM
  • ...

    My plan is that at some time in the future I backup the database and restore it to a hosting service.
    It seems that this won't work with an .mdf file.

    So, is it possible to recreate the master database from the mdf file?

    ...

    Well, once you have the mdf attached to the SQL Server Instance that you are actively monitoring/administering, you can take a valid backup from the database and there you go.

    It might even work with just the mdf, if they ust attach it to their instance. Won't be a problem in my eyes, since you can only copy/move the mdf when the instance using it has taken it offline (and checkpointed all data). I'd recommend Backup&Restore though.

    Don't mix it up with the "master" database. This is the system database, and I don't think you really want to deploy that one. :)


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Friday, September 27, 2013 6:39 AM
    Thursday, September 26, 2013 10:08 PM
  • Thanks for the advice.

    Yes, I do understand the difference between the SQL Server master database and what I chose to call the  master database of the application.

    So what procedure should I follow?
    Is this correct?

    Right now

    1: Create a new database instance in SSMS - lets call it DB1.
    2: Attach the mdf to DB1
    3: Backup DB1
    4: Continue with my testing.

    For deployment:
    Backup DB1 and restore it to the hosting service


    Ken Evans

    Friday, September 27, 2013 6:36 AM
  • Do you have .ldf file too?

    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, September 27, 2013 6:41 AM
  • If you do not have exact version of log file, see this link:

    Attaching a .MDF without a .LDF


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Friday, September 27, 2013 6:50 AM
  • Thanks for the tip.

    But it's OK - I can see the ldf file as well.

    The mdf and ldf files have the same timestamp.
    mdf = 12,480KB
    ldf = 2,816KB

    This looks ok .

    You agree?


    Ken Evans

    Friday, September 27, 2013 7:11 AM
  • ..

    Is this correct?

    Right now

    1: Create a new database instance in SSMS - lets call it DB1.
    2: Attach the mdf to DB1
    3: Backup DB1
    4: Continue with my testing.

    For deployment:
    Backup DB1 and restore it to the hosting service


    Ken Evans

    To make it accurate:

    1: You will not create a database beforehand- The term "Instance" on the other hand is the running SQL Server Service. Sounds like you have an oracle background, but just to make sure ;)

    2, 3, 4 and deployment are absolutely correct

    good lock


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Friday, September 27, 2013 8:04 AM
  • Progress!

    Not quite there yet but this is what I have done so far.
    1: Copied the mdf & ldf to a new folder.
    2: Right click "Database" in SSMS Object Explorer.
    3: Choose "Attach"
    4: Navigate to the folder that contains the mdf & ldf & click OK in the dialog box.
    5: I can now see the database and its tables in SSMS Object Explorer.

    So what's the problem now?
    Well, the attached database has a very long name
    The name begins with:  C:\PROGRAM FILES (X86)\......................MDF

    This name is NOT the name of the folder to which I copied the mdf & ldf files.
    This name IS the name of the folder from which I copied the mdf & ldf files.

    When I think of it this makes sense but I was a bit surprised when I first saw the name pointing to the "wrong" folder. I suppose SSMS got the name from the ldf. Right?

    Now my next question is:
    Is it OK to rename the file

          from: C:\PROGRAM FILES (X86)\......................MDF
          to: the name of the file that I accidentally deleted  (it is similar to "db01" )

    Thanks


    Ken Evans


    • Edited by Ken Evans Friday, September 27, 2013 8:06 AM typo
    Friday, September 27, 2013 8:06 AM
  • ...

    So what's the problem now?
    Well, the attached database has a very long name
    The name begins with:  C:\PROGRAM FILES (X86)\......................MDF

    This name is NOT the name of the folder to which I copied the mdf & ldf files.
    This name IS the name of the folder from which I copied the mdf & ldf files.

    When I think of it this makes sense but I was a bit surprised when I first saw the name pointing to the "wrong" folder. I suppose SSMS got the name from the ldf. Right?

    Now my next question is:
    Is it OK to rename the file

          from: C:\PROGRAM FILES (X86)\......................MDF
          to: the name of the file that I accidentally deleted  (it is similar to "db01" )

    ...

    great

    but are you sure you mean the "name" that is so long?

    I would suspect you mean the folder where it looks for the other file.

    The easiest way would be to get the Script for the current action, and then change everything there - or show it here first.

    You can try to change it in any case. Just make sure you don't end up in a situation where the mdf from the new folder is used but the ldf from the old folder...


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Friday, September 27, 2013 8:13 AM
  • Oracle? Oracle? Not quite. Never been anywhere near those folks.

    However, it is true that for about four years starting in 1975, I was working with an internal IBM relational product that was derived from System R.  Of course, this was before Larry and the others founded SDL and maybe even before Ed Oates had read the article in the IBM Systems Research Journal. (According to Wikipedia)


    Ken Evans

    Friday, September 27, 2013 8:23 AM
  • So is it a name?

    Is it a name?


    Ken Evans

    Friday, September 27, 2013 8:32 AM
  • So is it a name?

    Is it a name?


    Ken Evans

    :-D

    noooo, it's not a "name", it's just the "identifier"

    just kidding

    Yeah, it is. But now that you have attached it, it should be easy to just rename it.

    I suppose the mdf itself has a shorter and more meaningful name. Maybe use that one, so it's in sync


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Marked as answer by Ken Evans Friday, September 27, 2013 9:09 AM
    Friday, September 27, 2013 8:54 AM
  • Everthing is now working.

    So now I am back on square 1!

    Ladders 10 Snakes 9

    I really appreciate the help that you and Saeid have given to me.

    Thanks


    Ken Evans

    Friday, September 27, 2013 9:07 AM