diff options
Diffstat (limited to 'src/Migrating_SQL_Data.ascii')
| -rw-r--r-- | src/Migrating_SQL_Data.ascii | 110 |
1 files changed, 0 insertions, 110 deletions
diff --git a/src/Migrating_SQL_Data.ascii b/src/Migrating_SQL_Data.ascii deleted file mode 100644 index 7af806c..0000000 --- a/src/Migrating_SQL_Data.ascii +++ /dev/null @@ -1,110 +0,0 @@ -Migrating SQL Data -================== -:author: Aaron Ball -:email: nullspoon@iohq.net - - -== {doctitle} - -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: |