sql server programming: mdf file path in SQL Server 2005 connection string -- sql server programming

Re: mdf file path in SQL Server 2005 connection string

Stu
1/25/2007 9:00:00 PM

sql server programming:

I've never done this, but it looks like the following page has several
examples on connection to a SQL Express database in this fashion:
http://www.connectionstrings.com/?carrier=sqlserver2005

[quoted text, click to view]
fashion you are actually attaching the database to a server, so I would
assume that the database needs to be detached before you can use it (or
specify the database name which is used instead).

Perhaps someone with more experience can give some additional insight.

Stu

[quoted text, click to view]

mdf file path in SQL Server 2005 connection string

Max2006
1/25/2007 11:33:45 PM

Hi,

I understand that we can choose the .mdf database file through the
connection string in SQL 2005. Do we still need to use sp_detachdb when we
prepare mdf files for this feature?

Where can I get more information related to specifying mdf file path in
connection string?

Thank you,

Max

RE: mdf file path in SQL Server 2005 connection string

changliw NO[at]SPAM online.microsoft.com
1/26/2007 6:55:28 AM

Hi Max,
I understand that you would like to know how to connect to your SQL Server
with .mdf file and if the .mdf file need to be detached first.
If I have misunderstood, please let me know.

The .mdf file need not to be detached when you use it in your connection
string, since the SQL Native Client provider will automatically detect it
and avoid reattaching the file.
For the usage, please refer to the following sample:
==============================================
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(@"Data
Source=Charles\WOW;AttachDbFileName=C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\TestNull.mdf;Integrated
Security=true;Database=TestNull");
cmd.Connection.Open();
cmd.CommandText = "UPDATE Test SET [NAME]='TEST' WHERE [ID]=1";
cmd.ExecuteNonQuery();
cmd.Connection.Close();
=============================================

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

RE: mdf file path in SQL Server 2005 connection string

changliw NO[at]SPAM online.microsoft.com
1/26/2007 7:00:36 AM

Attach two additional links here for your reference:
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx

Using Connection String Keywords
http://msdn2.microsoft.com/en-us/library/ms254499.aspx

Charles Wang
Microsoft Online Community Support

Re: mdf file path in SQL Server 2005 connection string

Max2006
1/26/2007 11:20:08 AM

Hi Charles,

Thank you for help.

You said:

The .mdf file need not to be detached when you use it in your connection

String.

I am a bit confused about what you said.

My question is related to the deployment aspect of a .mdf data file. Let me
rephrase my question:

Do we need to detach the mdf data files before the deploying it to a new
location? I am sure that this is not true in SQL Server 2000. I wonder is it
the same in SQL Server 2005?

Thank you,

Max

[quoted text, click to view]

Re: mdf file path in SQL Server 2005 connection string

changliw NO[at]SPAM online.microsoft.com
1/29/2007 5:49:26 AM

Hi Max,
I am sorry for bringing you confusion since I did not notice that you were
trying to deploy it to a new location.

I think that I was confused with your description:
[quoted text, click to view]
There seems to be no relationship between sp_detach_db and SQL 2005
connectionstring. If you want to specify the .mdf file in your connection
string, it is no need to detach the database.
Could you please let me know what your real want is? or could you please
describe your senario more detailed?

From your reply, I understand that you want to move your current data files
to another location ( may be a network share or another hard disk), right?

For your question:
[quoted text, click to view]

If you use BACKUP/RESTORE, you do not need to detach the mdf data files
before deploying it to a new location.
You can use RESTORE DATABASE FROM DISK='backup file path' WITH MOVE
'filename' TO 'new_location_path';

If you want to use ALTER DATABASE or sp_attach_db, the data files are
required to use use sp_detach_db first.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Re: mdf file path in SQL Server 2005 connection string

changliw NO[at]SPAM online.microsoft.com
1/31/2007 11:57:54 AM

Hi Max,
Just check with you to see if you need further assistance on this issue.

If this issue persists, I would like your confirmation with the meaning of
your .mdf files deployment. Maybe I have some misunderstandings of your
issue. I appreciate that you could come back at your convenience.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.