1 Migrating SQL Data
2 ==================
3 :author: Aaron Ball
4 :email: nullspoon@iohq.net
5
6
7 == {doctitle}
8
9 In my current project I have found a need to migrate data from one SQL server
10 to another. For those of you SQL gurus out there, you know that there are many
11 ways to migrate data from point A to point B in SQL, even when different
12 versions is involved. Here's my setup
13
14 *Server 1:*
15
16 * SQL Server 2005 Standard (x86)
17 * Windows Server 2008 (x86)
18
19 *Server 2:*
20
21 * SQL Server 2008 Standard (x64)
22 * Windows Server 2008 (x64)
23
24 As you can tell, I'm upgrading versions *and* processor architectures.
25 Surprisingly enough, this didn't seem to cause any issues for me.
26
27 Here are a few options one has to migrate SQL data between servers for those
28 who don't find this post too useful.
29
30 * SQL Copy Database Wizard
31 * Detach, copy to new server, and reattach
32 * Backup database, copy backup to the new server, convert backup to a
33 database, attach the converted database
34 * Create a database mirror
35 * Duplicate the database structure on server two and import the data
36 from server 1
37
38 For my environment, only 1, 3, and 5 would work since the others leave more
39 possibility for data integrity issues during the transfer or require that the
40 SQL server be temporarily taken offline. I tried out a few of my options and
41 decided that 1, the SQL Copy Database Wizard, was the best option. It's
42 relatively straightforward and very efficient.
43
44 For the last three days I have been struggling with it because of what looks
45 like permissions issues, though I can't be sure since all the error says is
46 that step 1 was the last step to run and that the job failed (give me ambiguity
47 or give me something else!). All that being said, I decided I needed to find a
48 new way to transfer the data.
49
50 Through all of my troubleshooting I found quite a few SQL facets that I pieced
51 together to get what I needed. Here's how I chose to migrate my 12 databases
52 without too much trouble.
53
54 image:files/01_SQL_Migration_ScriptDatabaseAs.png[height=300]
55
56 To start, I used SQL Server's remarkable "**Script Database as**" functionality
57 to write out the query that creates the database, all of the columns, and all
58 of their constraints. For mine I just copied the script to the clipboard to
59 make compiling all of the scripts together much faster. To sum it up, I had SQL
60 generate the queries for each database I wanted to migrate and I pasted them
61 all into notepad so I could run them all at the same time.
62
63 image:files/02_SQL_Select_Import_Data.png[height=300]
64
65 After running all of the afforementioned queries to create the all of the
66 database structures on your destination server we're ready to start importing
67 data. * the database to import data to. Go to *Tasks* -> **Import Data**...
68
69 If you haven't done this before, you should receive a "Welcome to the SQL
70 Server Import and Export Wizard" screen. **Next**.
71
72 image:files/03_SQL_Import_Choose_DataSource.png[height=300]
73
74 Here we are at the "Choose a Data Source" screen. For Server name type the
75 hostname of the server you need to migrate from (the source server). After
76 that, select the database you want to copy to the new server. Once done with
77 that, * **Next**.
78
79 On the "Choose a Destination" screen, type in the name of the server to migrate
80 the data to. Next, select the Database you want to copy the data to (this
81 should corrospond to the DB name on the source server since we ran the create
82 queries generated by SQL). In my case, I was running the Import and Export
83 Wizard from the server I was importing the data to so SQL server already had
84 the Server name and Database filled out. **Next**.
85
86 In my case, I wanted to copy the entire database to the destination server, so
87 for the "Specify Table Copy or Query" screen, I elected to "Copy data from one
88 or more tables or views". **Next**. On the following screen, check all the
89 database tables you want to be copied (or just check the box at the top left
90 for all of them).
91
92 Nearing the end the "Save and Run Package" screen comes up. If you don't need
93 to save the package to be run later or again at a later time, just leave Run
94 immediately checked and click **Next**. Finally we review our settings and what
95 will be copied. If everything looks right, click Finish. Once the transfers
96 have completed, click **Close**. If any transfers failed or threw a warning,
97 you can click the "Messages" text next to the table that did not succeed to see
98 the log entries about it.
99
100 Repeat the aforementioned steps until you have migrated every database you
101 need.
102
103
104
105 Category:Microsoft
106
107 Category:MsSQL
108
109
110 // vim: set syntax=asciidoc:
|
