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

Francisco Tapia fhtapia at gmail.com
Tue Aug 29 16:33:07 CDT 2006


I'm also guessing we're working with SS2000, so you do want to patch to SP4.

On 8/29/06, JWColby <jwcolby at colbyconsulting.com> wrote:
> >This next q' sounds silly but... are you all patched up on your sql server?
>
> No that is not at all silly, and since the answer is "I don't know" the
> assumption could be "NO".
>
> I will look at that.
>
> 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 4:19 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Transferring a database to another SQL Server
>
> Yes when I ment navigate I ment via Windows Explorer.  I was only able to
> get to them via Sql Server after I updated the client network utility and
> made sure that both the TCP/IP Named pipes.
>
> This next q' sounds silly but... are you all patched up on your sql server?
>
>
>
> On 8/29/06, JWColby <jwcolby at colbyconsulting.com> wrote:
> > >I ask because I've had situations where you can see the other server
> > >by
> > navigating to it, but cannot register it locally.
> >
> > And what does this mean?  Navigate to it as in using Windows Explorer?
> > Or by using something inside of SQL Server?
> >
> > BTW, I moved the drives, remapped them on the new machine so that the
> > drives are mapped to the same drive letters that they were on the old
> > machine.  I can locate and attach only one of the three files that
> > made up the SQL Server on the other machine.  Now, when I detached the
> > drives in SQL Server on the original machine, I immediately went in
> > and reattached the files and all three reattached.  On the new
> > machine, the files can be seen by windows explorer, and can be found
> > and the attach attempted by SQL Server, but SQL Server claims that the
> other two files are not SQL Server files.
> >
> > Luckily the one big table APPEARS to be in then one that did attach
> > correctly.  Any way to figure out why the second and third files are
> > not attaching?  If trying to use just the one file, is this database
> > now corrupted?  I mean 2 out of three files are no longer there, and
> > they had SOMETHING in them because they would only compress to 50g down
> from 150g.
> > They might have had index stuff because the last thing I did was
> > create a PK
> > (autoincrement) which of course went out and built an index and stuff.
> > The PK field is in the table.  So se whether the index is though.
> >
> > 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 2:23 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] Transferring a database to another SQL
> > Server
> >
> > So I never knew if your machine could see the other, or was just not
> > registering on the 2nd server.  I ask because i've had situations
> > where you can see the other server by navigting to it, but cannot register
> it locally.
> >
> > On 8/29/06, JWColby <jwcolby at colbyconsulting.com> wrote:
> > > Thanks for this Francisco.  I am in the process of tearing my two
> > > machines apart to move the physical drives from one to the other.
> > > We'll see how it all goes.
> > >
> > >
> > > 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:02 PM
> > > To: dba-sqlserver at databaseadvisors.com
> > > Subject: Re: [dba-SQLServer] Transferring a database to another SQL
> > > Server
> > >
> > > In order to prevent orphaned users, MS put out a script called
> > > sp_help_revlogin you create it in the master database, details are
> > > on my blog,
> > >
> > > http://sqlthis.blogspot.com/2006/01/migrating-logins-from-one-sql-se
> > > rv
> > > er.htm
> > > l
> > >
> > > if you get lost from the links post back and I'll help you through it.
> > >
> > >
> > > 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
> > >
> > > _______________________________________________
> > > 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
> >
> > _______________________________________________
> > 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
>
> _______________________________________________
> 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...



More information about the dba-SQLServer mailing list