How do i move an SQLDatabase to another location?

[Tony Girgenti]

Hello.

I realize my other post was a bit lengthy, so i'd like to repost this
question.

Developing VS2005, SP1, .NET 2.0, VB Windows form program on WIN XP Pro,
SP2. It has a Dataset with two tables using an SQL 9.0 Server Express.

After creating a new database from the VS server explorer, it seems that the
new database always resides in \C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data.

When i create the datasource, it asks if i want to copy it to my project.
If i answer yes, i cannot update the database.

How do i move the database to another location?

Any help would be gratefully appreciated.

Thanks,
Tony

 

[William \(Bill\) Vaughn]

Where you place your database (.mdf and .ldf) file(s) is up to you. If you use the VS UI tools, there are a number of defaults that kick in and (as you found) SQL Server uses it's own "favorite" place to keep databases--you aren't given a choice to pick your own location. SQL Server Express Edition adds even more options (and complexity) as it permits you to have a user-instance of SQL server with its own (another) copy of the database. Yes, you can choose to put your database in the project as well. As I illustrate in the book, you can end up with 6 or more versions of the database in minutes. While each of these database instances can be updated, it might appear that they are not due to the

I'm of the opinion, that one needs to decide on a deployment strategy before clicking through the defaults. You need to decide if you want to have a shared server-based database, one that's installed independently on the client or one that's associated with your project. How you manage these SQL Server servers and instances and the databases you create is really up to you. Do the VS IDE tools help? Yes, but not very much. SQL Server Management Studio gives you a lot more options and (thus) control over what gets built where and when.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Tony Girgenti]

Hello Bill.

From your reply, i thought you were trying to give me a hint as to where to
look to figure out what i want to do. I read some of the SQL Server
Management Studio Express documentation and it does not seem to contain info
about what i want to do.

I guess i should have been more specific in explaining what i ultimately
want to do.

I want to be able to take a finished project with an empty database to a
client location, install it and have it run using the database without
having connection string, security, SQL error message and program bombing
issues.

I figured this could be done inside the program by creating some kind of
dynamic connection string, but i just don't know where to look to find out
how to do it.

Do i need to bring SQL Server Express with me to the clients?

Do i need to know what the connection string will look like before i go
there?

How do i determine if i want to have a shared server-based database or do i
want to have one that installed independently on the client?

I don't even know what these terms mean. I just want to be able to take my
stuff somewhere and have it work without getting a bunch of error messages.

Thanks,
Tony

Where you place your database (.mdf and .ldf) file(s) is up to you. If you
use the VS UI tools, there are a number of defaults that kick in and (as you
found) SQL Server uses it's own "favorite" place to keep databases--you
aren't given a choice to pick your own location. SQL Server Express Edition
adds even more options (and complexity) as it permits you to have a
user-instance of SQL server with its own (another) copy of the database.
Yes, you can choose to put your database in the project as well. As I
illustrate in the book, you can end up with 6 or more versions of the
database in minutes. While each of these database instances can be updated,
it might appear that they are not due to the

I'm of the opinion, that one needs to decide on a deployment strategy before
clicking through the defaults. You need to decide if you want to have a
shared server-based database, one that's installed independently on the
client or one that's associated with your project. How you manage these SQL
Server servers and instances and the databases you create is really up to
you. Do the VS IDE tools help? Yes, but not very much. SQL Server Management
Studio gives you a lot more options and (thus) control over what gets built
where and when.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[William \(Bill\) Vaughn]

Well, yes. SQL Server is not a "file-based" database like JET or SQL Server
Express Edition. It requires that the SQL Server service be installed and
configured (somewhere). This "somewhere" can be on the client system for a
single-user application or on a LAN/WAN share that's properly exposed by the
server and the service. This means that when you design your application,
you should have one of these scenarios in mind. It also means you'll
typically need to take steps to either ensure that the SQLEXPRESS instance
of SQL Server is running "somewhere" visible or install it yourself during
application setup. The new UserInstance approach can get you very close to
this scenario, but again, it requires SQL Server Express (and only Express)
be installed on the client system (along with your application). To that you
would copy your .MDF file which would be attached automatically the first
time it was opened.

Another approach you might consider is the SQL Server Compact Edition which
is NOT a service-type DBMS engine--it can be installed with or without admin
rights along with the single-file .SDF database.

I expect you would benefit a great deal from my new book that walks through
all of these issues from your point of view.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Tony Girgenti]

Hello Bill.

I'm just looking for an answer to where i can look to learn how to do what i
want to do.

These forums are not a place for you to sell your book.

Thanks,
Tony

 

[Earl]

That's funny. Almost. Very gracious too. Bill answers a ton of technical
questions that could make up many books if you took the time to algamate the
answers. Frankly I've found that without some books to put all the pieces
together, you are just floundering in the forums. There is simply no way you
can navigate through all of the different scenarios without a comprehensive
view of the problem and the various solutions. In your case, good luck
figuring out how to deal with the various idiosyncracies of an SQL
installation, or as you say, "take a finished project with an empty database
to a client location, install it and have it run using the database without
having connection string, security, SQL error message and program bombing
issues." That is a book all unto itself, encompassing ADO.Net, SQLExpress,
deployment, scripting, exception handling, and persistence of settings. You
might get some of those answers over in the SQL forums, but bring your heat
shields, as they are not generally as diplomatic.

 

[Tony Girgenti]

Hello Earl.

I'm new at all this and i might have more "luck" if someone wouldn't mine
telling me what kinds of subjects to research so that i can figure this
stuff out myself.

I'm not looking to the forums to give me the exact details of how to code
this project, i provide the details to people like Bill so that they can use
those details to point me in the right direction. I'm not asking for
anybody to navigate through any scenarios. I can do that. Just tell me
where to go.

I'm sure that Bill really knows what he is talking about because i see a lot
of his replies in these forums and i'm sure he knows a heck of a lot more
than i do. It seems the people in this forum hold him in high regard.
However, i don't like Bill pointing me to his website for answers.

Does the SQL forum answer questions about windows forms/database projects
and how to deploy them?

Thanks,
Tony

 

[RobinS]

I think you might want to take a moment and reflect on the fact that the
people who answer questions here are unpaid and are doing so in order to
give back to the community and be helpful. They are under no obligation to
answer any questions.

Bill's recommendation that buying his book would be greatly helpful to you
was an honest attempt to help you, just like I recommend his book or Dave
Sceppa's ADO.Net book or Francesco Balena's VB2005 book or Andrew
Troelsen's C# book because I think they are very useful. When someone asks
questions about something that is a large topic, even if their question is
specific, I sometimes refer them to a book, which is the same thing Bill is
doing. I may be able to give a short answer that helps them, but because I
don't always know the whole story, providing a reference where they can
research all of the scenarios is another way of being helpful.

Just my 2 cents'.
Robin S.
--------------------------

 

[RobinS]

And no, the SQLServer forum does not answer the kinds of questions you are
asking. This is the appropriate place to ask them.

Robin S.
-----------------------------------

 

[Tony Girgenti]

Hello Robin.

Well, looking at it that way certainly puts Bill's recommendation in a
different light. I understand now that Bill was simply providing me with a
possible resource to solve a problem.

I absolutely understand that the forum is totally volunteer. I've used the
Microsoft forums for years and am very appreciative of how i have benefited
from them. Please continue to provide the expertise. If the tone of my
replies to anybody here sounded demanding, i apologize for that.

These forums provide a great deal of information, are easily accessible,
extensive as well as free. One of my reasons for coming here is beacuse
they are free. Looking at Bill's suggestion about his new book from my own
personal perspective, at the time, i felt was inappropriate since i did come
here for free advice. Hopefully, someday, if i become knowledgeable enough,
i can provide some advice as well.

There has been a lot said in the past couple of days about my comment to
Bill, however, i'm still trying to figure out how to bring my stuff to
another computer and set it up to run with the existing database.

Bill's replies to me were very informative, but i'm still at a loss as to
where to look and what to read for help in solving my problem.

Thanks,
Tony

 

[RobinS]

Hi, Tony,

Yes, Bill was trying to help you. You don't expect him to type in a whole
chapter of his book and post it, do you? Apparently the problem you're
talking about has a lot of options around it, and his book explains what
those options are, and why you would choose one over another. He posted
what would be the short answer.

You may come here for a free answer, but it is a *privilege*, not a
*right*. And if a question has a huge answer, you may get "buy this book"
for an answer. Just because Bill suggested his own book, it doesn't mean
that's not okay. There are people who post broad questions like "how do I
save data to a database", and there are entire books written about that
topic. How are we supposed to answer that in one posting, and why?

I don't exactly understand what your problem was. You said you created a
new database, and it stored it under the SQLServer\MSSQL.1\MSSQL\Data
folder. It sounds like you're running SQLServer, not SQLServer Express,
when you create that database, but I can't be certain because I'm running
SQLServer, not SQLServer Express, and my databases go to a location I have
specified in advance. So do you have SQLServer on the machine where you're
creating that database? Or is it the Express version?

You said this:

When i create the datasource, it asks if i want to copy it to my project.
If i answer yes, i cannot update the database.

Don't say yes. I think if you do, it copies it over every time you run the
project.

How do i move the database to another location?

Like where? If it's SQLServer proper, you'll need to go into the Management
Studio and detach the database, move it, and reattach it. If it's a
SQLServer Express database, just move it and re-create your data source,
and when it asks if you want to copy it to your project, say no.

Robin S.
------------------------

 

[Tony Girgenti]

Hello Robin.

I feel very privileged.

Just to let you know. I went to my clients and installed SQL Server Express
and copied my database to the same location it was in while i was developing
it at my office(SQLServer\MSSQL.1\MSSQL\Data\ folder). I installed my
program and it ran without any problems. It found the database because the
connection string was the same as when i develop the program here i guess.

I know it might not be a big deal for some people, but i feel a lot more
comfortable now, knowing that i can bring a database with me and install it
for use at another location.

I am definitely using SQL Server Express. We were taught in school to
always copy it to our project folder when the question comes up. That way
the original database stays the way it is.

Thanks for all of your help.

Tony

 

[RobinS]

Hi, Tony,

Congratulations on getting it to work. Pretty cooool.

Robin S.
-----------------------