topic Re: SQL in BackOffice Products

SQL

andrew medhurst — Tue, 08 Mar 2005 04:35:55 GMT

hi guys i have note got a lot of knowledge on SQL mainly oracle/sybase,
i need to import a sql7 database export file with an extension of .MDF and a log file into an sql2000 sp3 database.
i have looked at the import option but i cannot see anything that looks for a file with the extension i have.
can someone tell me how to achieve this it is a fairly large db and i need to get it going.
regards
Andrew

Re: SQL

Norman_21 — Tue, 08 Mar 2005 04:46:44 GMT

Hi,

Use the .MDF database extension is recognized by SQL 2000 SP3 as well as .LDF log file extension?

sp_attch

http://www.15seconds.com/issue/050127.htm

Hope this help

Re: SQL

Edgar Zapata — Tue, 08 Mar 2005 04:48:54 GMT

First, I'm not a SQL specialist, but this might do the job:
Open up MS-SQL Server 2000 Enterprise Manager at the destination Server.
Select the SQL Server you want to import the .MDF file to.

Select DTS (Data Transforming Service)
Rigth clic on Local Packages, New Package
Choose a connection type: MS-Access or Text-File source.
Then choose "Microsoft OLE DB Provider for SQL Server" as the destination connection.
Then, select the third default Task: DTS

I hope it helps.
plz, report back.
regards.

Re: SQL

Norman_21 — Tue, 08 Mar 2005 04:51:19 GMT

Typ'o "sp_attach"

Sp_detach_db and Sp_attach_db Stored Procedures
To use the sp_detach_db and sp_attach_db stored procedures, follow these steps: 1. Detach the database on the source server by using the sp_detach_db stored procedure. You must copy the .mdf, .ndf and .ldf files associated with the database to the destination server. See this table for a description of the file types:

File name extension Description
.mdf Primary data file
.ndf Secondary data file
.ldf Transaction log file

2. Attach the database on the destination server by using the sp_attach_db stored procedure and point to the files you copied to the destination server in the preceding step.For additional information about how to use these methods, click the article number below to view the article in the Microsoft Knowledge Base:

http://support.microsoft.com/?scid=kb;en-us;314546#10

Re: SQL

Norman_21 — Wed, 09 Mar 2005 01:44:51 GMT

Andrew,

I'd appreciate keeping us updated!
Thanks

Re: SQL

andrew medhurst — Wed, 09 Mar 2005 03:46:08 GMT

Hi guys
I have eventually got this all sorted it would appear that your comments where correct and the file i had was a copy of the master DB file and once we resolved another issue i copied it onto the relevant area and attached it, it all seems to be as it should, was a bit confused to start ass i was told this was an export, which it wasn't.
so thanks again, all done.
regards
Andrew

Re: SQL

Norman_21 — Wed, 09 Mar 2005 04:07:14 GMT

Andrew,

After copying the DB file (MDF) and the Transaction Log file (LDF)and attach them successfully to the new SQL Server.
Restore the DB Backup from the old SQL Server to the new one if you have performed a backup newer than those (MDF & LDF) files.

Come back for any questions