Copying data from SQL 2000 to SQL 2014

SSIS and Import/Export
1

I am responsible for upgrading an old SQL 2000 database (and related processes) to SQL 2014. Our corporate database team helped a bit by transferring it over to a SQL 2008 Express database, which they said was a necessary step. I can back up and restore the 2008 db to 2014 no problem, but my issue is this: during development and testing, I need current data from the production SQL 2000 db. What is the proper way to refresh the SQL 2014 database with SQL 2000 data? Do I have to use the SQL 2008 Express db as an intermediary? Any info and details appreciated.

Note that each database is on its own Wintel server on the same domain, and I have full permissions to all. Thx.

2

If you use backup/restore, yes you will need to backup on 2000, restore on 2008, backup on 2008, restore on 2014. You could refresh your data directly from 2000 to 2014, but that'll required knowing the order of the tables (foreign keys) or dropping the foreign keys. Refreshing the data would potentially take longer than all of the backups/restores would. Plus refreshing could cause undesirable issues if not done right. Backup/restore is the best solution for upgrades, IMO.

2 Likes
3

Thank you, Tara. Exactly what I wanted to know. Much appreciated.