convert mdf+ldf files to another format (mdb,csv,etc)

how can i add more points to this?  I am a premium member, and tried to make it worth 5000 points, but the site only allowed 500, yet there are blurbs that say if you are premium you can use "unlimited question points". . ??

Hello antiphishing,

You cannot allocate more than 500 ponts to any question.  But with Premium you do not have a limit to how many questions you can ask. A non-Premium member has a specific number of points and can ask questions with a total points value only up to that specific number.
I can't help with your real question though.

Pete

as for your real question,. sorry but that CANNOT be done.  If it could, do you think MS wiuld EVER sell another copy of SQL Server.

If there were a simple way to convert an existing SQL Server database (MDF - main tables LDF log files) into another format, MS would have a HUGE exposure, and THAT will NEVER happen.

AW

antiphishing,

I do not know of any tools that will directly read an SQL database file - to create such a tool would require fairly intimate knowledge of the inner workings of MSSQL - which is not impossible but difficult enough to make your search for such a tool very hard.

In the event you do not find a tool - here is an alternative to buying MS SQL - use MSDE. Essentially it is exactly the same as MSSQL but has certain restrictions in terms of the number of concurrent connections it supports (10) maximum size of databse (2GB) but it supports everything that MSSQL does from a functionality perspective.

There are SQL client tools available on the net that would allow you to configure MSDE as you would need a SQL client access license to use the MS SQL Client Tools. The nice thing about the MS SQL Client Tools is that you can use the DTS to export data to pretty much anything.

This is only a suggestion though as there is no guarantee that MSDE will support your particular version of the database files.

A couple ideas:

1. Get a trial version of SQL*Server and export/extract data to another format.  I haven't used SQL*Server for quite some time, so I can't help with that but the trial version should work for you.

2. Oracle offers a free tool called Migration Workbench.  I have used it to migrate from Access to Oracle and I think it handles SQL*Server as well.  Oracle also has a trial version so you could use that to output your data to an ASCII flatfile if needed.  You can check that all out on Oracle Tech Net (otn.oracle.com).  That site does require a free membership account.

Good luck.
Stephen

Well it looks like it *CAN* be done, as apparently Oracle had succeeded (thanks andertst) however that just gets me converted to another format I can't use.  I need something more universally accessible such as the aforementioned mdb or csv.

Though, if I understand julianH correctly, I can just install MSDE somewhere here, then use query analyzer to attach the files (sp_attach_db), which I know how to do by example (from my earlier research).  Ok I'm downloading MSDE2000A.EXE from microsoft now to a test machine, annoying that I have to do this but since no one out there has written a mdf/ldf -> mdb converter, I guess this is the way it goes.  I'll report back.

Have you tried to connect to the database using ADO?  You should be able to connect using any one of a number of ODBC drivers.  Once connected, you should be able to use a FOR EACH loop to walk through the tables collection (FOR EACH Table IN Database.Tables) and then loop through the fields collection for each table (FOR EACH FIELD in Table.Fields) to get the table layout.  After that, create a recordset for the table and simply read each record and build your CSV string.  

This may or may not be easier than the MSDE route, but it's an option if MSDE doesn't support the version of MSSQL server you are trying to convert.  And if ADO doesn't work, there's always DAO.

Hope this helps

How can I use ADO/DAO?  You know what I have... mdf and ldf files... how do I get from here, to having foo.mdb?

Do you have VB or any programming language?  If you have Access, you have VBA, which will get you (kinda) where you need to go.

LandyJ,

I think you misunderstood the requirement. There is no Database. All Antiphishing has is the database file from an old SQL installation - what is need is something that can read that file, extract the data from it and write it to an MDB or CSV file. An ADO solution will only work if an MSSQL server is in place to which the database file has been attached.

The MSDE solution is about putting in place an MSSQL server so that the data can be read - after that has been done you can access the data in any number of standard ways.

My bad.

I have used ADO and DAO to access database files without having the underlying database engine installed, but it wasn't SQL Server (FoxPro and Paradox) and it was quite a few years ago. Jet and the odbc driver were able to figure it out.  I guess SQL requires a more powerful engine.

Sorry for the miscue.  In that case, I hope the MSDE route works for you.

LandyJ,

A quick point. There are two types of database systems file and server based. ADO can be used to access file database systems without any database application being installed MS Access is a case in point. Server based databases are different. When ADO connects to a SQL database it does not directly access the file in which the data is stored instead it submits the query to a RDMS which usually runs as a service or remote applicatoin. The RDMS evaluates the query, accesses the file and return the data. Without the RDMS (in this case MSSQL) ADO is useless as it has nothing to talk to.

Thanks, that's the peice I was missing.  I'll add that info to my lil' bag-o-tricks.

Ok I've got MSDE installed, can someone tell me what to do to get these old database files live so I can use the migration tools to pull this data out?  I don't see an "import old mdf/ldf files" button anywhere :)  Assume the files I have sitting here are:
foo_Data.MDF
foo_Log.LDF

Given that, what do I do? :)  Yes I'm an SQL dummy.  Help? :)

Antiphishing,

Ok, almost there - now for the moment of truth - will MSDE recognise your version of MDF.

The process of accessing the file is very simple

1. Open MS Access
2. Select File->New from the menu
3. From the New File options bar on the right Choose Project (Existing Data) from the New section
4. Select a location to write the project file (.ADP)
5. You should now get a standard SQL type connection dialog box. In the first text box (1) enter the name of the computer where MSDE is installed.
6. In section 2 select a Specific User Name and password radio dialog and fill in sa and blank password (this is the default for MSDE)
7. In section 3 select the Attache a Database File as a Database name radio option
8. In the text box below this give a name for the database (Can be anything you like)
9. Use the browse button [...] to find your MDF.

If MSDE likes your MDF Access will open the file and display it in the same way it handles normal MDB files. From here you can do with the data what you need to.

Good luck

i will try this today and come back and let you know thanks! :)

ok i can't even get msde working (its runs "fine" but never binds a listening port, I check with netstat), this is why I asked my original quesiton -- why can't i access this data WITHOUT running a !@#@!#$ @#$@#$ SQL SERVER!!

I will happily pay more to have my data converted than the cost of a shiney new SQL server from microsoft because (1) i hate them for making this so difficult they could have easily made an import function in access, and (2) because even if I had the full version here again I couldn't use it anyway it would take many hours of my time just to figure it out and I have a job to do and DBA isn't in the title.

What you said above, sounds great, but doesn't make any sense... you mean I can point at some sql server, and then a LOCAL file, and it will load the local file???  Then why wouldn't the samn thing just load the file?  Also if all I need in the remote database is to know the sa user/pass, then can I just borrow a friend's EXISTING sql server and just use that for this bootstrap procedure you outline above?  As soon as I get it to work I'll be happy to give you the points and be on my way, but I still can't get the data.... :/

thanks.

I apologize in advance if this suggestion is no good.  I actually wandered to this part of the forum because I needed a solution myself.

Client Tool Suggestion:

My understanding is that at this point you need a tool to easily configure and access the MSDE, which will then do the work on the mdf files. If that's the case, then why not install Enterprise Manager (which comes free with MS SQL Trial and does not expire when the trial expires)?

Password Confusion:

I don't think the password is stored in the 2 files that he has. Isn't it in the master.mdf file?

antiphishing, I am not sure if I get you correct, but I was in the same situation you were at.

This is what I had to do to get it to work.

I downloaded the SQL2KDeskSP3.exe from microsoft.  Installed it, which installed SQL Server.

Then go to MS Access and do this...

Open Access, then cancel the opening box
Click File then Open
Then under "Files of type" choose ODBC Databases ()
then choose the "Machine Data Source" tab
Your .mdf file should be in there...  if it is not, try throw your .mdf in here "C:\Program Files\Microsoft SQL Server\MSSQL\Data"

Once you do get to choose your .mdf file, open it...  what this will do is open all the objects (tables) in that .mdf file and export them into MS Access...   I did this and it seemed to work fine for me!  Be advised though, the way SQL handels data is different than the way Access does, so your tables may not be #1 strucured the way you want and #2 may not contain the data you need (as was my case)

GOOD LUCK!
Chance~

A couple of things

1. SQL2kDeskSP3 - is MSDE with SP3 - it is not the full version of SQL - just clarifying that point
2. Not sure how you can make an ODBC connection to an MDF file - only the SQL engine will open these files.
3. Before you can access an MDF file through SQL you first have to attach the database - SQL does not know the file exists until you tell it. You can do this either by using the process described above or by using the sp_dbattach stored proc.

I am not sure how you managed to open an MDF through an ODBC dialog.

Trust me...  it works...

In Access you are able to open an .mdf file through the ODBC... I just did it.

Follow my steps above and try it for yourself, believe me, I was skeptical at first as well, but seeing is believing.

Chance~

I don't mean to doubt you but I did go through the process and did not find what you describe.

Firstly, the odbc machine data sources are drawn from the registry not from the file system.
Secondly, ODBC does not know about the installation of sql server - it works on database drivers that are also listed in the registry. Therefore the physical location of the file is not a factor.

What you can do is access an MDF through Access and MSD using the process I described earlier in this thread i.e. you need a functioning version of SQL server. What Access does is it communicates through ODBC to the SQL server and issues an SQL attach command on the specified database - that works.

I am afraid I can find no reference anywhere to the functionality you are describing - there must be something specific to your installation that is allowing you to do what you say you can do but unless MS has radically changed something that I am not aware of the technology in question simply does not operate in the way you describe.

BTW - just to check I tested your procedure in three different machines with Access 2000, Access XP and Access 2003 - same results - they worked as expected without the functionality you claim.

Here is a test.

1. Create a file in notepad and put some text in it
2. Save the file as Test.MDF in the directory you describe above
3. See if this file appears in the Machine Datasources tab.

Also check the Machine data sources tab to see if Master.MDF, MSDB.MDF,Temp.MDF and Model.MDF appear in the tab as well - these are all valid MDF files that exist by default in that directory on every MS SQL installation - if they don't appear then your process does not work.

Another thing - open your registry editor and browse to the followign key

HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini

Check to see if there is an entry in their that refers to your MDF file.

I am very curious to find out what is happening on your machine.

Be advised I got this solution from ISS, this is the recommendation they provided me to open their .mdf file, I am not the one who came up with this so as not to be the expert on "exactly" how/why it works.

I did your test on creating a blank notepad template with the .mdf extension.  You are right, it did not show up, however my ISS .mdf file does show up...  why I am not sure.

I went to the registry and yes, there is an entry in there for my file, so you are probably right as in this is why I am able to open it.

I also do NOT have the Master.mdf, msdb.mdf, temp.mdf and model.mdf in my listing under machine data sources as you suggested, however I do have my files listed as system files and other user files as well (ex. DBase Files, MQIS, etc...) .  Maybe all this has something to do with the way ISS Scanner handles their .mdf files?

I am really not sure what to say as to why mine is showing up and as to how, all I know is that this process worked for me.

Good luck in finding a solution!
Chance~

JulianH

I tried your method and it worked perfectly.

I tried my method again and could not get the same result.

Thanks again for the re-clarification and an easier way for myself to do this!

Thanks
Chance~

Glad to have been of assistence