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

Jim Lawrence accessd at shaw.ca
Tue Aug 29 11:52:25 CDT 2006


Hi John:

If it just requires moving the index and data files to the other computer,
then attaching them that can be done as simply as copying the two important
files from computer A to computer B. Given a database named MyDatabase just
copy from directory ie: c:\Program Files\Microsoft SQL Server\MSSQL\Data
files named MyDatabase.ldf and MyDatabase.mdf to the other computer or
simpler yet just attach (MS SQL 2005), Microsoft SQL Server Management
Studio > databases > right-mouse-click > select Attach > select Add and then
just navigate to your computer that has the (data) MyDatabase.mdf file and
select it.

Is this what you are asking?

HTH
Jim 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, August 29, 2006 8:37 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Transferring a database to another SQL Server

>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




More information about the dba-SQLServer mailing list