I need to copy only data (not tables) from a SQL Server 2000 database to a SQL 2014 Express database. I tried to do this using DTS but it loses keys and indices. Backup (2000) and restore (to 2014) does not work. Is there another way?
Sql-server – Copy data from SQL Server 2000 to SQL Server 2014 Express
exportsql server
Related Question
- Sql-server – Unable to copy a DB from SQL Server 2000 to SQL Server 2012 using copy wizard
- Sql-server – Restore DB from SQL Server 2008 R2 to 2014 Express
- Sql-server – Migrating from SQL Server 2008 R2 to SQL Server 2014
- Sql-server – Converting a SQL Server 2000 Database to SQL Server 2016
- Sql-server – Copy whole database from SQL Server to SQL Server using linked server
- Sql-server – Regarding Sql server 2014 express edition
Best Answer
Copying ONLY data does not copy the keys and indices, since these are metadata describing how to treat the data.
The following article suggests that you create a SQL Server 2008 instance, backup the SQL Server 2000 database and restore to the SQL Server 2008 database. Then backup the 2008 database and restore it to SQL Server 2014. (Be sure to check Service Pack requirements).
http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx
Then you should have the SQL Server 2000 database upgraded to SQL Server 2014.
Since you want Keys and Indices as well, you need the metadata. Another approach is to script out all the objects (tables, views, procedures, etc) from your SQL Server 2000 database. Then execute the create script on your 2014 server. (Fixing any problems that you find.) After that transfer the data via linked server (per Mark Sinkinson), SSIS packages, BCP, etc.