Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a production environment running Windows Server 2000 and a development environment using SQL Server 2012. I need to make updates to the DB and implement some new features using .ASP. Is there a way to export the 2000 database and import it into 2012? All that I could find is this MS support doc about transferring the DB to 2008.

http://support.microsoft.com/kb/962355

share|improve this question
4  
No such thing as SQL Server 2003. Did you mean SQL Server 2005? –  Aaron Bertrand Jan 7 at 15:07
    
Sorry, you're right. It's Server 2000 –  adam2k Jan 7 at 19:20
1  
    
Also worth checking out Upgrade SQL Server 2000 database to to 2008 R2 and enable new features. Though it is for 2008R2, the steps still applies to 2012. Aaron pointed out that you have to have an intermediate 2005,2008 or 2008R2 server for doing an upgrade. Rest the pre and post steps remains the same. –  Kin Jan 7 at 20:32
add comment

1 Answer

up vote 8 down vote accepted

Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2).

On 2000:

BACKUP DATABASE dbname TO DISK = 'D:\backups\db.bak' WITH INIT;

On 2005 or 2008 or 2008 R2:

RESTORE DATABASE dbname FROM DISK = 'D:\restores\db.bak'
  WITH REPLACE, RECOVERY, MOVE (...you will likely have to specify new file locations...);

Then from there, back it up again, and then restore on the 2012 server.

Once you've restored, you'll probably want to adjust the compatibility level:

ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = 110;

As well as update statistics. But note that 80 compatibility will automatically be upgraded to 90 (and you will have issues if you use syntax that is no longer supported, e.g. *= joins). While it won't catch everything, you may want to run the 2012 Upgrade Advisor while the database is in its temporary home on a 2005, 2008 or 2008 R2 instance.

share|improve this answer
    
Thanks Aaron, I'm going to test this out this afternoon! –  adam2k Jan 7 at 16:21
    
I was able to backup the DB, but upon trying to restore this is the error that I received: italic The database was backed up on a server running version 8.00.0818. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. Msg 3013, Level 16, State 1, Line 1 italic –  adam2k Jan 7 at 19:16
1  
Then your source server is 2000, not 2003 or 2005. –  Aaron Bertrand Jan 7 at 19:18
    
@AaronBertrand: is it possible to restore a SQL Server 2000 database in SQL Server 2012? –  Mihai Bejenariu Jan 7 at 19:22
3  
@Mihai no, as I described in the answer, you need to restore it somewhere else first (2005, 2008, or 2008 R2), then backup that database, and restore that backup on 2012. –  Aaron Bertrand Jan 7 at 19:40
show 6 more comments

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.