Windows – Importing database from MS SQL Server 2003 to MS SQL Server 2008

sql serversql-server-2008windowswindows-server-2003windows-server-2008

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?

Best Answer

First of all there is no such thing as SQL Server 2003.

You'll want to take a full backup of the source database and restore it to the new SQL Server, probably using the WITH MOVE syntax to put the files into the correct drive letter location.

Don't use the copy data or copy database wizards. They are useless.

RESTORE DATABASE [001] FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE,
MOVE '001_Data' TO 'D:\Path\To\File\001.mdf',
MOVE '001_Log' TO 'E:\Path\To\File\001_log.ldf'

You'll need to figure out what the logical file names are from dbo.sysfiles on the old server and replace 001_Data and 001_Log with the correct logical file names.

Related Topic