Steps to Migrate from SQL Server 2000 to SQL Server 2014

Not every organization is able to keep up with all of the new SQL Server releases. In fact, there are a lot of businesses that are still on older releases like SQL Server 2005 and even older releases like SQL Server 2000. So what do you do if you have an older release like SQL Server 2000 and you’d like to upgrade to a newer release like SQL Server 2012 or SQL Server 2014? There is no direct migration path to SQL Server 2012 or SQL Server 2014 from SQL Server 2000 but you can get there by using SQL Server 2008 as in interim migration step. Migrating from SQL Server 2000 to SQL Server 2012 or SQL Server 2014 essentially requires a data migration because the host system is probably running an older OS like Windows Server 2000 or Windows Server 2003 and you’ll almost certainly want to move to a more modern platform like Windows Server 2012. Here are the steps to migrate from SQL Server 2000 to SQL Server 2014 (these steps are essentially the same to go to SQL Server 2012)

1. Install a separate copy of SQL Server 2014 – The first step is to install a separate instance of SQL Server 2014. This will typically be on a newer OS like Windows Server 2012 or Windows Server 2012 R2. This can be on either a physical system or it can be a VM. If this instance is on the same subnet as the original system if will need a different system name but that can be changed later.

2. Make sure your source SQL Server 2000 is at SP4 – Next you need to be sure that your source system is at Service Pack 4 (SP4). You can check the SP level using the following query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

If the system is lower than SP4 you need to install it before proceeding.

3. Make a backup of all of the databases that will be migrated – After you have made sure the source system is at SQL Server 2000 SP4 then you can go ahead and make a full backup of all of the databases that will be migrated.

4. Install an interim copy of SQL Server 2008 / R2 – Next go ahead and install an interim copy of SQL Server 2008 or SQL Server 2008 R2. This copy will only be used to convert the database backups from the older SQL Server 2000 format which can’t be restored to SQL Server 2014 to the newer SQL Server 2008 / R2 format which can be. You can install this SQL Server 2008 / R2 instance on any system but it would probably be the most convenient to install the temporary instance on your target SQL Server 2014 system.

5. Install the appropriate service pack for the interim SQL Server 2008 R2 release – If you’re using SQL Server 2008 then you also need to put on SP2. If you’re using SQL Server 2008 R2 then you need to be at SP1. For the record you could also use SQL Server 2005 SP4 but I would recommend using one of the newer releases.

6. Restore the SQL Server 2000 database backup to the interim SQL Server 2008 / R2 system – Once the appropriate service pack has been installed on the interim SQL Server 2008 / R2 system go ahead and restore the SQL Server 2000 SP4 database backups to the interim SQL Server 2008 / R2 server.

7. Backup the SQL Server 2008 / R2 databases – At this point the SQL Server 2000 SP4 database will have been restored to the SQL Server 2008 / R2 system and will have been converted to the newer format. Now you can make a backup of the databases on the interim SQL Server 2008 / R2 system. After this backup has completed you no longer need the interim SQL Server 2008 / R2 system and you can remove the instance.

8. Restore your SQL Server 2008 / R2 to SQL Server 2014 – The next step is to take the SQL Server 2008 / R2 databases are restore them to the target SQL Server 2012 or SQL Server 2014 system. Once the restore has finished the databases will have been successfully migrated to the new SQL Server 2014 format.

9. Move Logins and SQL Agent Jobs – The last step in the migration is to move any logins and or SQL Server Agent jobs from the source SQL Server 2000 SP4 systems. You can get a script that you can use to move the logins to the new SQL Server 2012 or SQL Server 2014 system from How to transfer logins and passwords between instances of SQL Server. You can see how you can use SQL Server 2000 EM to script your SQL Agent jobs at How to script jobs using Transact-SQL (Enterprise Manager). Microsoft also recommends that you run sp_updatestats when you’re ready to begin using the new database.