[dba-SQLServer] Transferring a database to another SQL Server

JWColby jwcolby at colbyconsulting.com
Tue Aug 29 13:28:33 CDT 2006


Francisco,

Yes, I could ping the other server.  I have a 4 (currently) machine WFW
network.  I set up the internal IP range to 192.168.122.100-200, simply
because I was getting collisions with clients when I tried to set up VPN.
All of the machines can ping the others.  I have always had issues with WFW
and file sharing, sometimes it works, sometimes it doesn't, not sure why.
IIRC there is a service that has to run on each machine which broadcasts to
the other machines the local assigned IP address of that machine, kind of a
"I'm here" kind of thing and also listen for such broadcasts from other
machines?  As I have said, I an not a notwork guy.

In any event I had one of my old (three years now I think) AMD64 2ghz 2gbyte
machines already running.  It had SQL Server installed on it and I was using
SQL Server on that machine to work on this rather large db.  The response
was not stellar when it came to doing things like shrinking a 200gbyte file
back down, creating an index on a field etc.  It would take off and run all
day, essentially preventing me from doing anything else on that table, and
really even on the SQL Server because so much horsepower was being used.
So, says I, I'll go get an AMD X2 3800, a new motherboard and see if that
helps, which is where I am now.  I got the new machine set up, installed SQL
Server on it and it couldn't "see" the other SQL Server instance running on
my existing server.  Nor could the existing SQL Server see the new SQL
Server.

In the past I had FOUR machines all running SQL Server and all could see
each other, i.e. if you wanted to register the others, they were available
and could be registered - at least IIRC.  This was about a year or so ago
now.

So anyways, here I am with a new machine and no "coms" between them.  And of
course now even my pitiful little file sharing is broke so I can't just copy
the files over the network, I have to physically move the disks.  I have a
gigabit switch between all of my machines and I am MOVING DISKS!!!  Yea, I
am not happy.

8-(

And if course I need to go study hard and become a SQL Server Admin and
Notwork admin so that I can dare to ask questions.

I'll figure it out eventually but right now I need to get some work done, so
I am moving disks.  

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
Tapia
Sent: Tuesday, August 29, 2006 1:03 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Transferring a database to another SQL Server

John,
   When you say you couldn't register the server on the other machine, were
you still able to ping it? get to it's unc name via \\ServerName?

these questions I never saw answered. (maybe I scrolled to quickly on your
posts)

On 8/29/06, JWColby <jwcolby at colbyconsulting.com> wrote:
> >The simple answer was given to him (JC) in the first post back to him.
> Which is why I never chimed in.
>
> >Detach it.
> >Copy it to the other machine.
> >Attach it.
>
> In fact that is only one of my two questions, but given my reception 
> on the other question I suppose I should just not ask again.
>
> In fact I have RTFM (in advance of even asking AAMOF) and I found 
> stuff about disconnecting / reconnecting possibly causing "orphaned 
> users", this being caused by the use of something like a PK from the 
> OS user to represent the user in the SQL Server login, and even though 
> the user name is the same on the two machines, the UserID is not 
> necessarily the same.  It does make me a bit leery of going the disconnect
route.
>
> I had hoped to get both machines seeing the other (SQL Servers) and 
> then see if I could just query directly out of a table in one SQL 
> Server and transfer the data into the same table (new/empty) in the 
> other SQL Server.  I know I can export the data to text files and then 
> re-import back in, but given the fact that the data is hundreds of 
> gigabytes, I don't even want to go there if I can avoid it.
>
> Unfortunately I do not know enough about SQL Server / networking to 
> troubleshoot the "why" of two servers not seeing each other, and given 
> that the list Gurus specifically want me to go become a SQL / Notwork 
> DBA before I ask such questions, it seems that I will get no assistance on
that here.
>
> I have the database on 5 250gb drives, which I suppose I shall 
> disconnect and then simply move the entire disks and pray for the best.
>
> There are other forums I belong to though, I'll also go see if anyone 
> can help.  This is going to be a lot of data and eventually I will 
> need machines preprocessing and then shipping the results to a central 
> server, so the servers really do eventually need to see each other I 
> think.  It should be fun, and I should learn a lot, though perhaps not
here.
>
> Thanks Stewart,
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert L.
> Stewart
> Sent: Tuesday, August 29, 2006 10:16 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Transferring a database to another SQL 
> Server
>
> As to why we are all here, it is to learn and hopefully help when we 
> KNOW the answer.
>
> The simple answer was given to him (JC) in the first post back to him.
> Which is why I never chimed in.
>
> Detach it.
> Copy it to the other machine.
> Attach it.
>
> KISS - Keep it Supremely Simple
>
> Always works for me.
>
> Robert
>
> At 08:40 AM 8/29/2006, you wrote:
> >Date: Tue, 29 Aug 2006 10:17:25 +0930
> >From: "Haslett, Andrew" <andrew.haslett at ilc.gov.au>
> >Subject: Re: [dba-SQLServer]
> >         [AccessD]TransferringadatabasetoanotherSQLServer
> >To: <dba-sqlserver at databaseadvisors.com>
> >Message-ID:
> >
> ><0A870603A2A816459078203FC07F4CD2BE403C at adl01s055.ilcorp.gov.au>
> >Content-Type: text/plain;  charset="utf-8"
> >
> >Suit yourselves - Seems to be a bit of a 'boys' club who are quite 
> >happy to ask for advice but only accept it when its something they 
> >want to hear, and ignore it otherwise (demonstrated throughout the 
> >post
> >below)
> >
> >The points I made in my post apply to anyone looking to undertake 
> >similar issues, and will continue to happen until someone has the 
> >guts to say it, irrelevant of who it is aimed at (which is how it 
> >should be
> >-> on a public forum, favour or protection should not be given to
> >anyone simply because they are friends, associates or seniority).
> >
> >Ask yourself this -> Would you have made the same post Jim, had it 
> >not have been aimed at Mr Colby?.
> >
> >Stark realities occasionally need be surfaced in order for them to 
> >sink in.. Irrespective of the target or consequences.  Was it harsh?
> >Yup.  Could it perhaps prevent someone else from making the same 
> >mistakes and undertaking projects without due preparation and planning?
> >Hopefully, and therefore I believe useful.
> >
> >John asked Stuart "Why are you here then?".   Some of us prefer to
> >assist others, pass on what knowledge they can and in my case attempt 
> >to 'help others, help themselves', as opposed to just feeding of 
> >others.
> >
> >I know of a number who have left this (and the Access) list in the 
> >past, including myself, for similar reasons and 'Matesmenship' in the 
> >upper echelons.
> >
> >Please unsubscribe me from the list.
> >
> >Andrew
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list