I have 2 MS SQL Servers running on 2 separate domains and I'm trying to import data from 1 to the other. The 1st MS SQL Server runs on Windows server 2003. The 2nd SQL Server runs on Windows server 2008 R2.
On 2008 I tried importing the data by using the Start->All Programs->Microsoft SQL Server 2008 R2->Import and Export Data (64-bit) tool. There it says that:
to move or copy databases and their objects from one server instance to another, cancel this wizard and use the Copy Database Wizard instead.
I was looking for the Copy database wizard based on this article. There it says that I should do the following:
In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.
I created a database for the occasion, but when I right-click on it and go to Tasks, I don't have the Copy database task.
The only 2 tasks which seems to allow me importing data are:
-Restore: there I can try to restore a backup of the database created under SQL Server 2003 but I get an error message saying the backup was created for a different database. I tried this solution which didn't work.
-Import data: this opens up the same Import and Export data tool mentioned above. If I go ahead, I can choose SQL Server Native Client 10.0 as a data source, the SQL Server 2003 as a Server name. However the Use Windows Authentication (2 separate domains) and the Use SQL Server Authentication options fail (I'm still investigating this issue).
Can someone help me by giving me directions as to how importing a database from a MS SQL Server 2003 to a MS SQL Server 2008 running on 2 separate domains?