SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2000 to SQL 2012 migration


SQL 2000 to SQL 2012 migration

Author
Message
sql_dba123
sql_dba123
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 538
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.
sql_dba123
sql_dba123
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 538
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
MarkusB
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5779 Visits: 4208
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.

Markus Bohse
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 4489
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
Jonas.SQL
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 59
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
shiv-356842
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 1220
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
Komodo_ER
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 88
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 Sad
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27928 Visits: 39921
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search