none
Planning to migrate SQL Server 6.5,7 and 2000 version to 2014

    Question

  • Hello team,

    We are going to migrate SQL Server 6.5,7 and 2000 version to 2014.Could you please help into this. what to follow and any documentation and application compatibility etc.

    Thanks in advance 

    Wednesday, November 16, 2016 9:06 AM

All replies

  • This may to big an ask when you consider the amount of deprecated features, data type etc between these versions and SQL Server 2014.

    For the 6.5 and 7 instances, backup and restore to the 2000 Instance, alter the compatibility Level to 2000.  Note that FileGroups were introduced with 2000 so even the backup/restore may fail.  If it does fail then create Databases on 2000 and use the 2000 import Wizard to import the data and objects.

    Check for any deprecated features/commands/data types and alter the 2000 Databases to suit.  I strongly recommend doing each change in turn and then backup the database every time a successful change is completed.   Note if you have use of the Database Upgrade Adviser use this Tool

    Once you have completed 6.5 and 7 upgrades to 2000 create a SQL Server 2008 R2 instance; and repeat the above process for the 2000 databases.

    Finally repeat for the migration from 2008 R2 to 2014.

    Note this is a brief overview of what is required; you will need to invest a lot of time to pull this off for all of the migrations; do not retire the older versions until you have verified that all of the Databases are functioning on 2014 as expected.  Keep careful notes throughout (one set of notes for each database).


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Wednesday, November 16, 2016 11:08 AM
    Wednesday, November 16, 2016 11:05 AM
  • Hi,
    There can not be direct migration from 2000 to 2014. There is one intermediate step you have to perform.

    Also you will need to run SQL Server Upgrade Advisor tool to find out compatibility issues in your current database. You will need to fix such issue if any before actual migration.

    Please go through this link which have information about migration and it will help you to proceed further.

    https://blogs.technet.microsoft.com/mdegre/2012/06/15/migration-sql-server-2000-to-sql-server-2012/


    Kindly mark the reply as answer if they help

    Wednesday, November 16, 2016 11:08 AM
  • For the 6.5 and 7 instances, backup and restore to the 2000 Instance, alter the compatibility Level to 2000.  Note that FileGroups were introduced with 2000 so even the backup/restore may fail.  If it does fail then create Databases on 2000 and use the 2000 import Wizard to import the data and objects.

    One cannot restore a 6.5 database to SQL Server 7 or 2000 because the storage architecture is different.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, November 16, 2016 11:37 AM
  • For the 6.5 and 7 instances, backup and restore to the 2000 Instance, alter the compatibility Level to 2000.  Note that FileGroups were introduced with 2000 so even the backup/restore may fail.  If it does fail then create Databases on 2000 and use the 2000 import Wizard to import the data and objects.

    One cannot restore a 6.5 database to SQL Server 7 or 2000 because the storage architecture is different.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    That's why I included this bit in case there was issues with the Backup/Restore...

    Note that FileGroups were introduced with 2000 so even the backup/restore may fail.  If it does fail then create Databases on 2000 and use the 2000 import Wizard to import the data and objects.


    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, November 16, 2016 11:41 AM
  • That's why I included this bit in case there was issues with the Backup/Restore...

    I saw that. I added this was not possible so that surya didn't go through the futile attempt of trying it in the first place.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, November 16, 2016 1:06 PM
  • Filegroups were introduced in 7.0.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 16, 2016 1:31 PM
  • That's why I included this bit in case there was issues with the Backup/Restore...

    I saw that. I added this was not possible so that surya didn't go through the futile attempt of trying it in the first place.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Thanks Dan

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, November 16, 2016 1:43 PM
  • Filegroups were introduced in 7.0.

    Tibor Karaszi, SQL Server MVP (Web Blog)


    Sorry Tibor for the mistake

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, November 16, 2016 1:43 PM