Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  user64204 Aug 24 '11 at 12:49
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
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? –  user64204 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
add comment

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.

share|improve this answer
    
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. –  user64204 Aug 24 '11 at 13:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.