SQL 2000 to SQL 2012 migration

Question

SQL 2000 to SQL 2012 migration

sql_dba123

SSC Eights!

Points: 853
More actions
December 19, 2011 at 1:12 am

#248253

Is it possible to migrate from SQL 2000 to SQL 2012 directly? Looks like direct upgrade using setup is not possible as per the below link:
http://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx
But is it possible to migrate using backup and restore? For example, set up a new 2012 instance, create all logins and other required objects and finally restore SQL 2000 DB backups.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sql_dba123 SSC Eights! Points: 853 More actions · Answer 1

I tried backup and restore option, that is also erroring out. Is there any other direct way of upgrading from 2000 to 2012 other than having an intermediate 2008/2008 R2 instance and again backing up from there and finally restoring on 2012 instance?

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

I would try using SSIS to import the SQL 2000 database.

I can't try myself right now, but I would think that's your best bet.

[font="Verdana"]Markus Bohse[/font]

crazy4sql SSCoach Points: 19590 More actions · Answer 3

No. To upgrade any database to 2012 which is below 2005, you have to first upgrade the database to 2005/2008 and then only will be able to upgrade to 2012.

----------
Ashish

Jonas.SQL SSC Veteran Points: 263 More actions · Answer 4

I did a couple of 2000 to 2012 migrations in a current project and it works really good to go by 2008 R2 instance. If you do the restore by T-SQL you can see the upgrades being done to the database. Don't forget to set the compatibility level to 110 afterwards.

shiv-356842 SSCarpal Tunnel Points: 4310 More actions · Answer 5

U can't migrate please follow below link

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/4dddd9e9-a34f-43d0-89bc-86672bebf52f

Tx

Komodo_ER SSChasing Mays Points: 657 More actions · Answer 6

MarkusB (12/19/2011)
I would try using SSIS to import the SQL 2000 database.
I can't try myself right now, but I would think that's your best bet.

Hey MarkusB!

If I am not mistaken SSIS wasn't there in SQL 2000, I believe you are referring to DTS.

That actually gives me an idea to move the 2000 DB to 2005 / 2008 instance (DTS -> SSIS) then move the new database to 2012. Any ideas ?!?!

Sorry I cannot try this myself as I do not have 2000 instance 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 7

what he meant was your SSIS in 2012 can connect to any SQL 2000 instance, and thus do the database migration.

no need to use DTS and then SSIS if SSIS alone could do all the work.

I'd stick with the suggestions relating to restoring the database on a 2008 or R2 instance, then doing a backup and restoring that on your 2012 instance.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!