SQL Questions: Attaching Databases to SQL Server

[Jonathan Wood]

On my development machine, I've been creating databases as standalone
database files rather than creating them under SQL Server Express, and then
specifying AttachDbFilename in the connection string for testing.

This has worked well: My SQL Server Express is not cluttered with test
databases, and I haven't had to deal with all the connection errors that no
one could explain when I try to access a SQL Server Express database.

But now I need to transfer my database to the server that is hosting my
Website.

I was looking into the tools at www.simego.com (for importing data into a
database, and for synchronizing databases between desktop and server). This
tools only appear to work with databases that are actually attached to the
server. So I have the following questions:

1. If I attach a database to the server (however that is done), can I do it
temporarily and then detach it after I'm finished?

2. If so, how would I temporarily attach it, and how would I then detach it?

3. I also have access to SQL Server 2005 and the Management Studio that I
understand comes with it. Does anyone have any opinions as to whether I'm
better off with the simego.com tools or SQL Server 2005 (keeping in mind
that I'm trying to keep this as simple and painless as possible)?

Thanks!

 

[Norman Yuan]

If you are talking to attach SQL Server database to SQL Server hosted by
your service provider, you need to have appropriate privilidge for that.
Unless you use SQL Server Express' USER INSTANCE and attach the db
dynamically (I doubt ISP would use SQL Server Express, though).

See more inline.

On my development machine, I've been creating databases as standalone
database files rather than creating them under SQL Server Express, and
then specifying AttachDbFilename in the connection string for testing.

This has worked well: My SQL Server Express is not cluttered with test
databases, and I haven't had to deal with all the connection errors that
no one could explain when I try to access a SQL Server Express database.

But now I need to transfer my database to the server that is hosting my
Website.

I was looking into the tools at www.simego.com (for importing data into a
database, and for synchronizing databases between desktop and server).
This tools only appear to work with databases that are actually attached
to the server. So I have the following questions:

1. If I attach a database to the server (however that is done), can I do
it temporarily and then detach it after I'm finished?

Yes, you can, as long as you have proper access to the server where SQL
Server runs

2. If so, how would I temporarily attach it, and how would I then detach
it?

If you can attach it (either run sp_attach_db, or use SSMS/Enterprise
Manager), you can detach it (use sp_detach_db or SSMS/Enterprise Manager).
Again, you need to have proper access to the SQL Server box.

3. I also have access to SQL Server 2005 and the Management Studio that I
understand comes with it. Does anyone have any opinions as to whether I'm
better off with the simego.com tools or SQL Server 2005 (keeping in mind
that I'm trying to keep this as simple and painless as possible)?

I do not see the need to something else other than SSMS, if you can connect
SSMS to the SQL Server box (you must contact your ISP to see if they allows
you to connect your SSMS to their SQL Server. I'll bet "NO"). Your ISP may
provide a way/tools to allow you upload your database (MDF/LDF) files and
attach to their SQL Server. Or you have to write SQL script to do it. Only
your ISP could provide details on how you can set up your SQL Server
database on their SQL Server box.

 

[Jonathan Wood]

Norman,

Yes, you can, as long as you have proper access to the server where SQL
Server runs

Here, we are talking about my development computer. Yes, I have sufficient
priviledges on my development computer.

If you can attach it (either run sp_attach_db, or use SSMS/Enterprise
Manager), you can detach it (use sp_detach_db or SSMS/Enterprise Manager).
Again, you need to have proper access to the SQL Server box.

Okay. Unfortunately, I just don't know what that is. And I'm scared to death
that I'm going to start some process and be held up for days trying to
figure things out as I'm going to be pressed for time.

I do not see the need to something else other than SSMS, if you can
connect SSMS to the SQL Server box (you must contact your ISP to see if
they allows you to connect your SSMS to their SQL Server. I'll bet "NO").

What does this mean? The other server will be the one that is hosting my
Website. You are saying you think they won't allow me to connect to my
database on the host?

Your ISP may provide a way/tools to allow you upload your database
(MDF/LDF) files and attach to their SQL Server. Or you have to write SQL
script to do it. Only your ISP could provide details on how you can set up
your SQL Server database on their SQL Server box.

I don't understand what this has to do with my ISP. I have one computer on
my desktop, and another that is part of a shared Web-hosting account.

The hosting company recommends accessing my database using "Enterprise
Manager," which they say can be downloaded from microsoft.com. But I
couldn't find it and I have no idea what this is.

Thanks.

 

[Norman Yuan]

Are you saying that you have your website hosted somewhere and you want your
web application to access SQL Server data on your development computer? Then
you need to solve other more important network issues than
attaching/detaching database to SQL Server.

Norman,


Here, we are talking about my development computer. Yes, I have sufficient
priviledges on my development computer.


Okay. Unfortunately, I just don't know what that is. And I'm scared to
death that I'm going to start some process and be held up for days trying
to figure things out as I'm going to be pressed for time.

Attaching/detaching database to/from SQL Server is easy, with or without
using SSMS/Enterprise Manager. Unless your database is huge (hundreds GB),
it will not take "days"

What does this mean? The other server will be the one that is hosting my
Website. You are saying you think they won't allow me to connect to my
database on the host?

It is possible the ISP does not allow their SQL Server being connected from
outside without extra security measures, especially from the Internet (or I
will not choose them as my ISP). You need to contact them t see how you can
access their SQL Server, web server. If the web server is theirs, and the
sql server is on your computer, I do not see how the two servers could be
connected to each other via the Internet.

I don't understand what this has to do with my ISP. I have one computer on
my desktop, and another that is part of a shared Web-hosting account.

Again, you need to contact your ISP to know how to access SQL Server (if it
is also hosted by the ISP). Also, thing could be simple when you do your
development with your computer where web server (IIS) and SQL Server is on
the same computer. At ISP level, it is most likely, the web server and SQL
Server are in different computers. If you did not test your web app
thoroughly and understand ASP.NET security configuration/SQL Server security
configuration well, your app propably will stop working, and debugging app
on ISP hosting server will be very difficult, if not impossible.

The hosting company recommends accessing my database using "Enterprise
Manager," which they say can be downloaded from microsoft.com. But I
couldn't find it and I have no idea what this is.

You cannot download "Enterprise Manager", which is lisenced part of SQL
Server2000. If you are using SQL Server2005/Express, Enterprise Manager will
not work anyway. You need SSMS (lisenced part of SQL Server2005), or SSMS
Express (free download from MS).

Since your ISP is recommending Enterprise Manager, I suspect they still use
SQL Server2000, while you are developing with SQL Server2005. You need to
contact them to see if they are hosting SQL Server2005 or not. SQL Server
2005/Express database cannot be attached to SQL Server2000.

 

[Jonathan Wood]

Norman,

Are you saying that you have your website hosted somewhere and you want
your web application to access SQL Server data on your development
computer? Then you need to solve other more important network issues than
attaching/detaching database to SQL Server.

I only want to access SQL Server data on my development computer for A)
testing, B) to create the initial data, and C) so that I can transfer that
data to the host server. When complete, the data and application will be on
the same server.

Yes, I need to solve other problems as well--but that doesn't help here.

Attaching/detaching database to/from SQL Server is easy, with or without
using SSMS/Enterprise Manager. Unless your database is huge (hundreds GB),
it will not take "days"

Yes, this stuff is always easy until I play with it--then it seems it never
works. So last night I figured out that SSMS is SQL Server Management Studio
and I cranked that up (the Express version). When I click the Add... button
in the Attach Databases dialog, I get the following error:

<<<<<<<<<<<<<<<<<<<<<<
Microsoft SQL Server Management Studio Express

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)

The server principal "Jonathan-PC\Jonathan" is not able to access the
database "model" under the current security context. (Microsoft SQL Server,
Error: 916)
<<<<<<<<<<<<<<<<<<<<<<

Again, you need to contact your ISP to know how to access SQL Server (if
it is also hosted by the ISP).

The server hosting my Website is unrelated to the people who provide my ISP.
I don't think there is any connection there.

Also, thing could be simple when you do your development with your
computer where web server (IIS) and SQL Server is on the same computer.

I have SQL Server Express on the same computer where I do my development.
But since the computer that will host my site and database is in another
state, I won't be doing my development on that computer.

Since your ISP is recommending Enterprise Manager, I suspect they still
use SQL Server2000, while you are developing with SQL Server2005. You need
to contact them to see if they are hosting SQL Server2005 or not. SQL
Server 2005/Express database cannot be attached to SQL Server2000.

It is not my ISP, it is the company hosting my Website. They are using SQL
Server 2005.

Thanks.