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