How do I find my MSSMS database?

Question

0.00/5 (No votes)

See more:

Sounds silly I know, but I created a database in MSSMS, filled it with data and now can not find it anywhere. I can open it in MSSMS, do anything I want to it but can not find it anywhere on my computer. I have used Explorer with many configurations and it can't find it. I even searched using just '*.mdf' still didn't find my file, I've manually looked everywhere I can think of with no luck.

Does anybody have any idea Where oh where has my .mdf gone?

darby
"When you were born, you cried and the world rejoiced.
Live your live so that when you die
the world cries and you rejoice!"
~Old Indian Proverb~

What I have tried:

Manual searches, and Windows Explorer using many different search criteria

Posted 27-Aug-18 4:41am

Member 10376725

Updated 27-Aug-18 5:40am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-08-27T05:41:00

Solution 1

Open SSMS, and right click the main server name node in the "Object Explorer" pane.
Select "Properties", and in the resulting dialog select the "Database Settings" page from the list on the left.
Near the bottom is the "Database default locations": the "Data" box gives the folder where SQL Server stores its database files.

Posted 27-Aug-18 5:41am

OriginalGriff

Comments

Member 10376725 27-Aug-18 11:52am

Thank you, I found it. I wonder why Explorer didn't find it.

OriginalGriff 27-Aug-18 12:01pm

Possibly because it's not "yours" - it's owned by the agent user that runs SQL Server, not by the user you log in as.

Member 10376725 27-Aug-18 13:21pm

That needs some explaining. Anyway, it did bring up another problem, when I tried to attach the database to a VB project it said I don't have permission to use the file. So, I changed the permissions to allow everyone access and now it tells me the file is open in another program. For the life of me I have no idea what it is talking about.

Member 10376725 27-Aug-18 13:22pm

Who is the agent if not me, I'm the only one that uses this computer and installed everything myself.

OriginalGriff 27-Aug-18 14:21pm

Oh boy ... :laugh:

You don't "own" your computer, not any more - the OS does, and it decides what you are allowed access to. You aren't the only user, not by a long shot, and most of the "not you" users are for things like Services - which because they run before you log in cannot run under your user ID. And SQL Server is a service, which runs under its own agent ID.

Try this: run Task Manager (CTRL + SHIFT + ESC will get you there), make sure you are in "more details" mode (if it says "Fewer details" in the bottom left, you are), and click the "Details" tab. You see a column "User Name", which will contain you, SYSTEM, and a bunch of other names, who are also users but who can't really log in.

SQL Server runs under its own user(s), probably "LOCAL SERVICE" and "MSSQL$" plus the instance name. That's the user that "owns" the databases, not you! And it's for good reasons: firstly so that it can act as a service to other computers, even if you don't log in this week; and second so it can "grab" the database files to prevent anyone else from messing about with them. Once you give SQL a database, it doesn't like to let go! :laugh:

Why are you trying to find the actual files?

Member 10376725 27-Aug-18 18:19pm

I have a Visuak Basic program that I am working on. I have the main progam on my Desktop but am working on an upgrade for it. I'm doing this on my laptop for the reason of portability along with the fact I didn't want to take a chance of corrupting the original data. I created a duplicate database on the laptop and, using a .txt file, i ported the data from the main database to the new one. All worked well, but now I want to attach the new database to the VB program and ran into the above problems. I changed the permissions, but now it says the file is open in another operation. So now what??? I understand the whys, but not letting me access a database I created seems a bit counter productive to me.

Member 10376725 27-Aug-18 20:49pm

Hey OriginalGriff, what happens if I end the MSSQL$SQLEXPRESS task?

OriginalGriff 28-Aug-18 3:02am

Not sure if you can: it's owned by a different agent, so you may get a polite "go away and stop being silly" message.
But if you do, then SQL services stop being available. It's possible that they might auto restart the service task when your code starts to connect, if the service is set for that, but I doubt it - I've had occasions in the past when SQL failed to start on my dev machine and I have to manually start it in the Services app in order to work with DB's.

And you may lose data or leave your DB in an inconsistent state.

I'd leave it alone - start or stop it in the Services App to be on the safe side.

Member 10376725 28-Aug-18 12:07pm

All I know is I have this database with over 800 movies in it, and I want to create a UI that will allow me to be able to View, Add, Delete, and any other manipulations I want to do to it and I cant even access the data. I already have a similar program with a similar database and no problems. Why can't I make it happen in the new program? And what do I have to do to make it happen?

as I stated earlier, it doesn't make sense that I can create a database then not be able to access it with a VSVB program. Or C# or C++ for that matter. I just prefer Visual Basic.

darby

OriginalGriff 28-Aug-18 12:18pm

You've given it to SQL - which is a good idea - so let SQL handle it!

You access the DB via SQL commands from your chosen language, be it VB, C# or C++
You establish a connection via an SqlConnection object, and use SqlCommands and / or SqlDataAdapters to issue SELECT, INSERT, UPDATE, and DELETE operations.

Using con As New SqlConnection(strConnect)
	con.Open()
	Using cmd As New SqlCommand("SELECT iD, description FROM myTable", con)
		Using reader As SqlDataReader = cmd.ExecuteReader()
			While reader.Read()
				Dim id__1 As Integer = CInt(reader("iD"))
				Dim desc As String = DirectCast(reader("description"), String)
				Console.WriteLine("ID: {0}" & vbLf & "    {1}", iD, desc)
			End While
		End Using
	End Using
End Using