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.Where did you get the files? Do they know that you have them?|||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.|||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.|||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|||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.|||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|||You're a genius Brett. Hey, I stole a script from someone that looks just like that. (grin)|||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

export data from excel to MDF

Hi,

I have some newer software which utilizes the SQL Desktop Engine (MSDE) and some older 16-bit application. I want to export the database from the old app to the newer software. If I can export the data from the old app to an excel spreadsheet, can I import that into the MDF file? Is this at all possible? What kind of other software would I need? This is my first experience in this area.

ThanksI haven't worked with MSDE, but do you have bcp?

bcp is the M$ Bulk Copy Program|||edit: it appears that I do have bcp|||bcp server.owner.table IN c:\filename -Sservername -Uuser -Ppaswword -c

Do you have access to books online?

Check out an Enigma post for the link if you don't|||Are more ideas on this? I am not familiar with creating tables and adding data etc. let alone working with bcp.

Export data dump

I would like to export the database(with table schema and existing data).

My database is a mdf file and i know that SQL Server 2005 Express Edition cannot do this.

I would like to know if there are any FREE tools can export the table schema and existing data to a .sql file. OR SQL Server 2005 can do this?

Thank you

You can actually do with Express if you have Backup and Restore permissions or Detach and Attach permissions or to run a DTS/SSIS package in the server where you need to upload your database. You can download the Express Advanced version which comes with Management Studio so you can use the wizard to move the database, I also have a faq that will help you with all the ways to move a SQL Server database. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

http://forums.asp.net/thread/1454694.aspx

Export and Import Database from Sql 2000 to Sql 2005

Need help exporting a database from Sql 2000 Exterprise and Import it to Sql 2005 using Sql Developer.

I have tried to do a detach - copy mdf & ldf to new syatem and then do an attach - This doesn't seem to work

Also tried backup on 2000 and then Resore on 2005. This doe not seem to work.

Source system is 64bit and destination system is 32bit

Not 100% certain but the problem is probably with the 64bit to 32bit conversion problem. The binary files are probably incompatible.

Try and straight db copy Management Studio by right clicking the on the database | Tasks | Copy DB. Make sure not use the detach and re-attach method.