Results 1 to 9 of 9
  1. #1
    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.

  2. #2
    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."

  3. #3
    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.

  4. #4
    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."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,910
    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

  6. #6
    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.

  7. #7
    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.

  8. #8
    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.

  9. #9
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •