how to integrate a MDF file into sql server?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 29 Sep 2007 15:36 GMT

Hi,

i have a MDF sql server express database used by an asp.net application.
I would like to integrate it into Sql Server express.

In Studio Management, i rightclicked on Databases and i took 'Attach'. Now i
can see and manage that database from Studio Management, but it still refers
to the local path of the MDF file. How can i make it a full database of Sql
server?

Thanks
Bob

Reply to this Message

Jeje - 29 Sep 2007 15:54 GMT

does the MDF file exists on the local drives of the SQL Server server?

you can't attach a database which is on your drive into SQL Server running
on another computer.

so first:
copy the MDF and LDF files on the SQL Server server local drive.
do the attach process (you can select the mdf file from anywhere from a
local drive of the server)
click ok
and its done.
verify the security after you have attached the database.

is it what you do?

> Hi,
>
[quoted text clipped - 8 lines]
> Thanks
> Bob

Reply to this Message

Bob - 30 Sep 2007 10:37 GMT

Thanks for replying.
I did all this, but when i do
select * from master..sysdatabases

in Studio Management, i still read as filename the local path
(c:\myapp\app_data) instead of :

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydb.mdf

So if i remove mydb.mdf from its local path, i have no access anymore to
that db.

The mydb.mdf is attached but not integrated into Sql Server.

You see what i mean?

> does the MDF file exists on the local drives of the SQL Server server?
>
[quoted text clipped - 23 lines]
>> Thanks
>> Bob

Reply to this Message

Ekrem Önsoy - 30 Sep 2007 12:13 GMT

Hello Bob!

I think you made it like this:
-Stoped SQL Server service and copied your mdf and ldf files to "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" and restarted your SQL
Service.

You should go this way:
Connect to your SQL Server instance and then detach your database (mydb or
whatever). Move your mdf and ldf files (related to mydb) to their new
location which must be "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\" I guess. And go to SSMS and attach your database
from the new path.

You should use detach\attach while moving databases, otherwise there will be
junkies of those databases in master database which is not good and could be
confusing later.

Signature

Ekrem Önsoy

> Thanks for replying.
> I did all this, but when i do
[quoted text clipped - 39 lines]
>>> Thanks
>>> Bob

Reply to this Message

Jeje - 30 Sep 2007 14:53 GMT

now I see...

SQL Server don't copy the database files into the default DATA folder.
Simply because SQL Server can access database store anywhere on the server
second because a DBA generally move the database to a different set of
disks.
it's why the attach process just attach the database from the location you
specify without moving the files.

a database is created in the DATA folder only when you CREATE a new database
WITHOUT specifying the storage folder.

the database is always "integrated" into SQL Server (using or not the DATA
folder is not important)

so if you want your database into the DATA folder, detach the database, move
the files into the DATA folder yourself and attach the database again using
the DATA folder instead-of the myapp one.

> Thanks for replying.
> I did all this, but when i do
[quoted text clipped - 39 lines]
>>> Thanks
>>> Bob

Reply to this Message

Bob - 30 Sep 2007 18:11 GMT

Thanks to both

> now I see...
>
[quoted text clipped - 59 lines]
>>>> Thanks
>>>> Bob

Reply to this Message