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.
1.) SQL Copy Database Wizard
2.) Detach, copy to new server, and reattach
3.) Backup database, copy backup to the new server, convert backup to a database, attach the converted database
4.) Create a database mirror
5.) 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.
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. Right click 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. Click 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, click 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. Click 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". Click 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.
