copying .mdf files from one server to another...

[abacaxi]

Hello,

I have SQL Server 7.0 and I am taking several .mdf files from one SQL Server to another. On the destination server, how can I open incororate these into a database? Can I directly insert them into the Data folder under the MSSQL folder? Or do I have to convert it to some kind of .csv file and then import into a database?

Getting these .mdf files from the source server is my only option! So I have to find a way to read these.

Thanks,

Todd

 

[LFCfan]

Hi Todd

check out ATTACH and DETACH in books online - it's v. simple

From EM, just right click on Databases in the destination server tree, choose Attach Database, and then it all becomes clear

Hope this helps
LFCfan

 

[abacaxi]

Hey LFCfan,

Thanks for the quick response and suggestion. I'll give it a try right away!

Thanks,
Todd

 

[abacaxi]

LFCfan,

When in EM, I right click on Database and I do not ses it!

Todd

 

[LFCfan]

Doh!

right click on Databases, the All Tasks> Attach Database

Sorry )

 

[abacaxi]

hmmmm...very weird.

I don't find the 'Attach' option anywhere, not even in the place you suggested!

This is 7.0 with no service packs installed...I wonder if that makes a diff?

T

 

[LFCfan]

Bizarre
i'm on SQL2000, but still, i'm sure that's been around since then

check out the following (in bold) in BOL. you need sysadmin privileges for this

use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go

 

[cmgaviao]

I'm not positive, but I believe this option might only have become available in 2000.

the following code will work just as well...mind you sometimes there is an issue with orphaned users...

sp_attach_db @dbname 0N 'putthedbnamehere'
@filename1 = N'c:\pathgoeshere\filename.mdf',
@filename2 = N'c:\pathgoeshere\filename_log.ldf',
go

 

[cehepker]

Hello,

Another way might be to just back up the database on the one server and restore it on the other server. You may need to move the physical file that you backed up to, but other wise it is very straight forward.

Carla

Documentation: A Shaft of light into a Coded world

 

[jopab]

I have problem with attaching a database in SQL server 2000. Our Systems Ad had a problem with one of our server and as far as i can tell, he had to re-install SQL Server on the same PC. Now, when i use the attach database option from the E.M., i cannot find my database. I don't know whether to use sp_attach_db or not. The physical files (*.mdf and *.ldf) still exists in the PC, but i cannot see it from the list of databases in the E.M.. Also, i don't think we have a backup of the system files. So, can i still use sp_attach_db to re-attach the database? ( i don't think the database was detached properly or if ever was detached).Any suggestion will do. Thanks.

 

[synapsevampire]

Since you know whtta the files exist, you must know where they are, so try using Explorer to check the attributes and permissions on the files.

-k