3

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?

Improve this question
2
  • 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, 2011 at 12:21
  • don't be sorry, that's just your opinion.
    – Max
    Aug 24, 2011 at 12:49

3 Answers 3

Reset to default
2

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.

Improve this answer
4
  • 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, 2011 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, 2011 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, 2011 at 16:25
  • I've added syntax to my answer with how to use the WITH MOVE syntax.
    – mrdenny
    Aug 24, 2011 at 16:27
0

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.

Improve this answer
1
  • 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, 2011 at 13:15
0

I know this is very late but its here for posterity. The easiest solution I found (as a novice) was to NOT create the database to start with. Instead select "Databases" in SQL Server Management Studio, right click and select "Restore database" enter the name for the new database and then the backup file and restore. I was using SQL Server Management Studio 10

The important point is if you create the database within 2008 and then try and import an old DB into it they are not compatible.

This page goes through the process. http://www.techrepublic.com/blog/windows-and-office/how-do-i-restore-a-sql-server-database-to-a-new-server-80916/

Improve this answer
4
  • That's not true, you can restore a full backup from an old version on a database created in version 2008 (I have done it with databases as old as SQL Server 2000). Restoring a full backup overwrites the database completely, no data or configuration is mixed (the only setting that can be preserved is the location of database files).
    – Alberto Martinez
    Oct 18, 2017 at 0:03
  • From what I could see it depended on how the database was created. reading other advice I first created the database in 2008. I then tried to restore over it with the replace command from a DB backed up from an old SQL 2000 instance. It failed. I then deleted the database and used the process above. it imported fine. Once I had done that I could restore with replace from the old 2000 no worries. So I believe that if you create the DB in 2008 its not compatible.
    – DeveloperChris
    Oct 18, 2017 at 0:12
  • When you restore from a backup of another server/database on top on an existing database, even if the other server use the same version of SQL server, you have to select the correct location of the data/log files (usually it maintains the path and file names of the original server/database) and check the option of overwrite the current database, otherwise the restore operation will fail. If you restore from backup without creating first the database you don't have to do that.
    – Alberto Martinez
    Oct 18, 2017 at 0:59
  • That may be the missing step. I will try it out when I get a chance. Thank you
    – DeveloperChris
    Oct 18, 2017 at 4:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .