How do I transfer tables from remote SQL database to local MDF file?

[pbb72]

Hi all,

Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.

I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.

Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.

Thanks,
Peter

 

[SQLBill]

You are slightly confused or mis-understanding.

MDF and LDF files ARE the database. The MDF file is the data file for the database and the LDF is the transaction log file for the database.

Do you have SQL Server on your local system? If not, you need to get it installed.

If so, you have two options:
1. detach the remote database, copy the .mdf & .ldf files, move the copy to your server, attach them on both servers. See the BOL (Microsoft SQL Server Books On Line for detach and attach directions/commands).

2. Do a backup of the remote database, copy the backup file to your server and restore it. (Again, see the BOL for backup/restore commands).

You CANNOT backup the .mdf/.ldf files and restore them. You CANNOT copy the .mdf/.ldf files when the database is in use (well you can, but they will be unusable).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875