# Importing database from MS SQL Server 2003 to MS SQL 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?

-
Sorry this is out of the scope of this site. Backup your data on the one server and restore it on the other. This is how Microsoft wants it and documented it in the Online and Offline documentation. – mailq Aug 24 '11 at 12:21
don't be sorry, that's just your opinion. – Max Aug 24 '11 at 12:49

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.

-
Upon restoring the backup I was getting the The backup set holds a backup of a database other than the existing '{DBNAME}' database error. I tried to use the Overwrite the existing database (WITH REPLACE) option which made no difference. When I try to restore the backup with a query (RESTORE DATABASE 001 FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE, 001 being the actual db name) I get a syntax error on the database name. Could you explain in more details how you would perform the restore with the WITH MOVE please? – Max Aug 24 '11 at 14:59
You probably need to wrap the database in square brackets in order to get past the error. I expect you will also need to add the "with move" syntax, but one thing at a time right? RESTORE DATABASE [001] FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE – Jason Cumberland Aug 24 '11 at 15:19
I agree that you'll probably need the command to be "RESTORE DATABASE [001] FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE" as SQL probably isn't going to like the database name starting with a number. – mrdenny Aug 24 '11 at 16:25
I've added syntax to my answer with how to use the WITH MOVE syntax. – mrdenny Aug 24 '11 at 16:27

The simplest thing to do would be to backup the database on the source server, transport the backup to the destination server, and restore it on the destination server. This can be done directly from SSMS.

-
I did try that (see "Restore" paragraph). The exact error message I'm getting when doing so is The backup set holds a backup of a database other than the existing '{DBNAME}' database although the name of both databases match. – Max Aug 24 '11 at 13:15