If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > export data from mdf and ldf files

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Posts: 3
Question export data from mdf and ldf files

Hello,

My first post, I don't know much about MS SQL.

I have the .mdf and .ldf files from an MS SQL 2000 server. I don't have MS SQL server running on my home machine. I'd like to be able to extract the data from the .mdf file to something I could use on my home machine. XML would be good, CSV would be ok too, maybe even some way to import into MySQL. Any help would be appreciated, also if I've some how missed the forum faq, please point me to it.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,634
Where did you get the files? Do they know that you have them?
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Posts: 3
Its from work, they asked me to see about moving the db to mysql. And since they asked me, I'm sure that qualifies as they know I have them. If it makes you feel better you can make your solution require that I go back to the machine with the MS SQL server. If there's some program on that machine that can export the data to a format I can use, that will be fine too. Not ideal, but I can do it.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,634
You can use "Proxy table" feature of MySQL, but you'll have to have network connectivity with SQL Server when you do that. If you have VPN connection configured to your work place, you're good to go. Defficiency of MySQL in that respect lies in the fact that you'll have to create those proxy tables one-by-one. But once you did them all, - do whatever you want with them using your favorite MySQL tools, - extract, import, transform, etc.

EDITED: I got totally confused. I was thinking about iSQLAnywhere, not MySQL. Check if MySQL has something like "linked server" to hook you up with the SQL Server.
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,744
If you are taking the files home, I'll assume that they are smaller than 2 Gb. If the files are smaller than 2 Gb, you can attach them to MSDE on your home machine and use them "as is".

Or better yet, you can buy SQL Server Developer for around $49 US and then you can do all kinds of fun things with them! This would give you a lot of tools (such as DTS and SQL Enterprise Mangler) that will make the job of porting data to MySQL many, many times easier!

If you don't want to bother with that, you can go back to the orifice and use BCP to put the data into flat ASCII files, that you can import with MySQLAdmin or a similar tool.

-PatP
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Posts: 3
The data is actually only a few megabytes, it was running on MSDE at the office. I'm reading up on bcp now, bcp looks like that'll be the easiest for me to do.
Reply With Quote
  #7 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Code:
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
	  @dbName sysname
	, @fp varchar(255)
	, @User varchar(255)
	, @Pwd varchar(255)
AS
/* 
	EXEC isp_bcp_out_database
		  'Northwind'
		, 'd:\Data\Northwind\'
		, 'sa'
		, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
	SELECT  -- 'EXEC Master..xp_cmdshell ' +
--		  '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
		  'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
		+ 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat  '
		+ '-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
		+ '-f'+@fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt ' 
		+ ' > ' + @fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
		-- + ', no_output' AS CMD
	  FROM    INFORMATION_SCHEMA.Tables
	 WHERE    TABLE_TYPE = 'BASE TABLE'
      ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @CMD

WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @CMD
		SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
		EXEC master..xp_cmdshell @CMD 
		SELECT @CMD = @fp + '\bcpout.bat'
		SELECT @CMD
		insert a (s)
		exec master..xp_cmdshell @cmd


		FETCH NEXT FROM bcpout INTO @CMD
	END

CLOSE bcpout
DEALLOCATE bcpout

	select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City, MO
Posts: 734
You're a genius Brett. Hey, I stole a script from someone that looks just like that. (grin)
__________________
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA.
Reply With Quote
  #9 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Quote:
Originally Posted by derrickleggett
You're a genius Brett. Hey, I stole a script from someone that looks just like that. (grin)
Why Thank you..thank you very much....x002548 has left the building
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On