Locating an MDF file to attach a DB using SQL Server 2005 Management Studio EXPRESS

I am trying to locating an MDF file to attach a DB using SQL Server 2005 Management Studio EXPRESS.

i am puzzled that I can open up child directories in Program files, all the way down to C;\Program File|SQL Server| etc etc, but cannot use the  "Locate Database File" Dialogue to find a MDF file in my "My documents". Am I going mad? It allows me to get to "C:\Documents and Settings\MyFolder" and no further.

I'm sure there's an easy answer.

Thanks in advance

jonesgjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Can you just do a search on your server for any files w/ the extension *.mdf?
0
maxis2cuteCommented:
the default path is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  all teh mdf's should be there
0
chapmandewCommented:
Hopefully you didn't place the data files in the default spot on the C: drive....not usually a good idea.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

jonesgjAuthor Commented:
Guys,

I am presuming that the MDF file can be anywhere on my machine - for example in my Visual Studio Projects Folder in My Documents. This is where I want to go to attach my MDF file, but am not allowed to navigate lower than My Documents.

Even with SQL Server 7 and 2000 you could point the server at vitually any location. I must be doing something worng (?)
0
chapmandewCommented:
If you for sure know where the file is located, then don't worry about navigating to it in the explorer, just type it in the location box...even if it is on a different machine.

\\MachineName\Drive$\....\FileName.mdf
0
maxis2cuteCommented:
if your are using windows use the search for *mdf

go to start search, under file types, type in *.mdf.  when it finds one cut and paste the location into sql manager .

0
jonesgjAuthor Commented:
Almost Chap and Maxis.....

I cut n paste the location and file name directly into the dialogue, and it accepts it: it adds it to the next dialogue. However when I then click OK it errors telling me it can't find the file. Whne I closed the dialogue the database has successfully attached! So I tried another database, I get the same errors but this time its not added.

Here is part of the error:


TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Attach database failed for Server 'NC6230\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Directory lookup for the file "C:\Documents and Settings\Joebloggs\My Documents\Visual Studio 2005\Projects\SQLServerExpressTest\SQLServerExpressTest\AlphaTest.mdf" failed with the operating system error 3(error not found). (Microsoft SQL Server, Error: 5133)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476

------------------------------

Attach database failed for Server 'NC6230\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files, String owner)

===================================

Directory lookup for the file "C:\Documents and Settings\JoeBloggs\My Documents\Visual Studio 2005\Projects\SQLServerExpressTest\SQLServerExpressTest\AlphaTest.mdf" failed with the operating system error 3(error not found). (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476

------------------------------
Server Name: NC6230\SQLEXPRESS
Error Number: 5133
Severity: 16
State: 1
Line Number: 1


------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
0
maxis2cuteCommented:
thats a long path.  Please for testing copy the mdf and ldf to the root c:\ just to rule out path errors
0
jonesgjAuthor Commented:
Yep ... I started this way when I could navigate where I wanted. That is it works fine on the root c directory.

0
maxis2cuteCommented:
i know these are simple steps but it will help me help you.

can you navigate to that path in my computer and see the mdf?

this looks odd to me  SQLServerExpressTest\SQLServerExpressTest.

it is basically telling you it cant find the path to that mdf.  Also if the my documents folder is really not loacted on your machine becasue it has been moved by folder redirection that may be a problem.

i would move it back to c:\  if that works move it to "c:\documents and settings\", etc until you find where the error is in your unc path.

or got to my computer and find the error that way.


0
jonesgjAuthor Commented:
maxis,

Yes this is a genuine path. I can navigate to this and work on the database (and another database) in my Visual Studio Projects directory.

I can attach directly on C:
I have now tried to store the mdfs and logs in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

... but here's the strange thing. It attaches, but reports that I am looking at the file in the Visual Studio projects folder. I have to run some SQL to see which db is actually in use.

I was hoping this was going to be a quick fix!  I am out now for a day or two on a training course - back Tuesday evening UK time.

Thanks for your help so far.

0
maxis2cuteCommented:
let me know when you are back
0
Anthony PerkinsCommented:
Excuse me if I am asking an obvious question, but why do you want to attach the database?  Is it not already attached to the server?  And is the server on your box?
0
jonesgjAuthor Commented:
Hi - back now.

ac I'm not an expert, but I want to experiment with replacing MS Access in my desk top applications with SQL Server 2005 express. I can connect to the MDF file from within the Visual studio environment but find its functionality limited (I have used Enterprise Manager an Query Analyser and advanced DTS in SQL Server 2000) so I thought it would be better to use the SQL Server 2005 Management Studio Express to set-up my database before (and maybe during) the work building my application. Anyway, the functionality is there to attach your database, it just doesnt seem to work properly.

Any advice gratefully received.

Thanks
0
chapmandewCommented:
webtubbs:  I don't remember what I posed?  Was it a link to another site or something?
0
maxis2cuteCommented:
if you would just like to manipulate your db.  then even though i use sql i have found for sql express it is easier to manipulate with access and just link the DB so any info you save in access you can uplaod to SQL and you never has to use Microsofts limited SQL tools.


it will take all the tables, primary keys, etc....
0
jonesgjAuthor Commented:
Hi Maxis,

I find Access a little restrictive. Don't get me wrong - Access is great, and I use it for all my 'hobby' work. But SQL Server with T-SQL is much better. The problem with Visual Studio is the inability to load your tables with external data. I have found I can use the DTS tool to do this - now called the data import wizard. This is accessible from the SQL Server 2005 Management Studio Express  - external tools - once you have downloaded the tool.

I haven't got my head around how best to use SS2005 Express yet, and deployment will be another issue I'm sure. But it would be good to get an answer to why the Locate Database File Dialog restricts your view in the My Documents folder.



0
maxis2cuteCommented:
I use SQL exress and hae not had this problem.

If you go to sql server mgt and the main page asks what server name is etc.. and you connect correct?

then in object explorer you should have your sql server and below that DB's, expand DB, RT click on DB and choose attach.

then go to the add tab in the attach db window and you should be able to find your drives, etc.  and attach it that way.  

if you can see it and have permissions you can attach it.  If you can see it and cant attach it check the permissions.

please update.

0
jonesgjAuthor Commented:
Maxis,

Yes, exactly what I do. However expanding Documents and Settings, finding my logon name (my personal folders) I click on this and the + sign disappears but the sub folders are not displayed

I am using Windows authentication... what else can I check for and where?



0
maxis2cuteCommented:
I do not think the path of my documents and settings\username  is ever going to let you use this, only because, it allows you to chose that path that no one else can ever get to to use the database since they would need permisions to that path and there is a .00009% chance of that happening so it blocks that path.

Even on my own which is jsut tried it will not showl futher down the path on myself as admin or any other user i have on that machine.  You are going to have to change the path.

Sorry,

0
Anthony PerkinsCommented:
>>Anyway, the functionality is there to attach your database, it just doesnt seem to work properly.<<
What you are missing is that in MS SQL Server you very rarely attach a database (certainly not one that is already attached to a server).  All you have to do is connect to the server and the databases should be there.  You are wasting your time tracking down where the database files and transaction logs are stored in the file system.

0
maxis2cuteCommented:
a little more research reminded me of the sqlcmd -S Server\Instance

try this link to show how to attach a db using that command
http://technet.microsoft.com/en-us/library/ms165673.aspx
0
maxis2cuteCommented:
aceperkins, this is sql express, not MS SQL, as far as i know in order to use the DB you must attach it to the SQL server or there is no way to use the DB
0
Anthony PerkinsCommented:
>>this is sql express, not MS SQL, as far as i know in order to use the DB you must attach it to the SQL server or there is no way to use the DB<<
Nope.  That is simply not true.  Other than some minor limitations in size there is no basic difference between the MS SQL Server Express engine and MS SQL Server.

So to summarize typically there is no reson to attach to a database using MS SQL Server in ANY edition.  This is NOT file based database system like MS Access.
0
jonesgjAuthor Commented:
Guys,

I have used SQL Server 7 and 2000 in a professional capacity and yes you can use the attach database file command. After all, when you create a new database you are effectively creating at least one file  - I think it defaults to xxx.MDF or NDF. You can if you wish choose multiple files at this point to address I/O bottle necks in your disk arrays or SAN configurations. Yes its a server, and yes you can have multiple instances, though its not a good idea for your main production environment.

Where I am coming from:

I am trying to learn SQL Server 2005 Express as it was advertised as a successor for Access in Desktop applications. I am quite happy with Access, but would like to learn about the express edition. Also I can leverage my SQL Server T-SQL knowledge and tools to make life a little easier.

Express still has an engine and 'appears' to run like a server. I saw understanding that as the next step. Understanding what "service" needs to be parcelled up with your application set-up program .

Maxis - I had a problem with a service after a recent server move, and you may have something with the security access issue. Just taking it that Express works like a server - it can be used as such in a small scale, then there is much sense in the reason why I cannot get to My documents\my folders...

So despite the dialogue coming up .... it must be restricted becuase its running in the context of the local system account? Maybe?
0
maxis2cuteCommented:
but the final test would be the sql command, if you can create and attach to the path documents\my folders... sqlcmd -S Server\Instance  if it does not work than i am sure it is a permissions issue since we know that it works from c:\ root etc...

Also i use sqlexpress when i deploy prepacked software since it travels well and can run locally on the machine it is deployed on.  I know where you are coming from and i agreee with your using SQL.  

Just try the create/attach and that should be that if it does not work then we know it has to be in an area that is accessable.  or try giving everyone rights to your documents for a test.

Let me know
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jonesgjAuthor Commented:
Maxis - didnt prove it categorically but cannot think of a better solution. It must be a security issue. What I did do was re-create the entire documents and setting folder structure direct of the C: and it worked like a dream. I think when I deploy the app I will have to consider security more so than Access. I will be posting a question tomorrow re deploying SQL Server 2005 .... if you have time maybe you can give me the benefit of your experience. Thanks for your help.
0
bravekindCommented:
I was going a bit crazy tyirng to use SQL Server Management studio to view my mdf file in my visual studio project. I changed the security on My Documents folder (I unchecked 'make this folder private'), then I could see the file but received a 'no editor available' error when I tried to attach it. In desperation I simply copied the files (.mdf and log) from my visual studio project directory to my C:\Program Files\...\MSSQL\Data directory. I can now attach this file and it appears to keep the reference path to my project file intact.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.