Migrating SQL Data

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:

Server 2:

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.

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.

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.

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.

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