Migrating SQL Data
==================
:author: Aaron Ball
:email: nullspoon@iohq.net



In my current project I have found a need to migrate data from one SQL server
to another. For those of you SQL gurus out there, you know that there are many
ways to migrate data from point A to point B in SQL, even when different
versions is involved. Here's my setup

*Server 1:*

* SQL Server 2005 Standard (x86)
* Windows Server 2008 (x86)

*Server 2:*

* SQL Server 2008 Standard (x64)
* Windows Server 2008 (x64)

As you can tell, I'm upgrading versions *and* processor architectures.
Surprisingly enough, this didn't seem to cause any issues for me.

Here are a few options one has to migrate SQL data between servers for those
who don't find this post too useful.

* SQL Copy Database Wizard
* Detach, copy to new server, and reattach
* Backup database, copy backup to the new server, convert backup to a
  database, attach the converted database
* Create a database mirror
* Duplicate the database structure on server two and import the data
  from server 1

For my environment, only 1, 3, and 5 would work since the others leave more
possibility for data integrity issues during the transfer or require that the
SQL server be temporarily taken offline. I tried out a few of my options and
decided that 1, the SQL Copy Database Wizard, was the best option. It's
relatively straightforward and very efficient.

For the last three days I have been struggling with it because of what looks
like permissions issues, though I can't be sure since all the error says is
that step 1 was the last step to run and that the job failed (give me ambiguity
or give me something else!). All that being said, I decided I needed to find a
new way to transfer the data.

Through all of my troubleshooting I found quite a few SQL facets that I pieced
together to get what I needed. Here's how I chose to migrate my 12 databases
without too much trouble.

image:files/01_SQL_Migration_ScriptDatabaseAs.png[height=300]

To start, I used SQL Server's remarkable "**Script Database as**" functionality
to write out the query that creates the database, all of the columns, and all
of their constraints. For mine I just copied the script to the clipboard to
make compiling all of the scripts together much faster. To sum it up, I had SQL
generate the queries for each database I wanted to migrate and I pasted them
all into notepad so I could run them all at the same time.

image:files/02_SQL_Select_Import_Data.png[height=300]

After running all of the afforementioned queries to create the all of the
database structures on your destination server we're ready to start importing
data. * the database to import data to. Go to *Tasks* -> **Import Data**...

If you haven't done this before, you should receive a "Welcome to the SQL
Server Import and Export Wizard" screen. **Next**.

image:files/03_SQL_Import_Choose_DataSource.png[height=300]

Here we are at the "Choose a Data Source" screen. For Server name type the
hostname of the server you need to migrate from (the source server).  After
that, select the database you want to copy to the new server. Once done with
that, * **Next**.

On the "Choose a Destination" screen, type in the name of the server to migrate
the data to. Next, select the Database you want to copy the data to (this
should corrospond to the DB name on the source server since we ran the create
queries generated by SQL). In my case, I was running the Import and Export
Wizard from the server I was importing the data to so SQL server already had
the Server name and Database filled out.  **Next**.

In my case, I wanted to copy the entire database to the destination server, so
for the "Specify Table Copy or Query" screen, I elected to "Copy data from one
or more tables or views". **Next**. On the following screen, check all the
database tables you want to be copied (or just check the box at the top left
for all of them).

Nearing the end the "Save and Run Package" screen comes up. If you don't need
to save the package to be run later or again at a later time, just leave Run
immediately checked and click **Next**. Finally we review our settings and what
will be copied. If everything looks right, click Finish. Once the transfers
have completed, click **Close**. If any transfers failed or threw a warning,
you can click the "Messages" text next to the table that did not succeed to see
the log entries about it.

Repeat the aforementioned steps until you have migrated every database you
need.



Category:Microsoft

Category:MsSQL


// vim: set syntax=asciidoc:
