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?
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).
- Upgrade path: SQL Server 2000 SP4 to SQL Server 2008
- Upgrade path: SQL Server 2008 SP3 or later to SQL Server 2014
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.
Use Generate Script option in SQL Server Management Studio, Follow these steps
- Right click database
- Go to tasks
- Select generate scripts
- Select script data option to true
- Click next
- Choose tables
- Click next
- Select Output option to file or New Query window
Click next and Finish your script wizard
Run those generated script in Other SQL Server.
Check moving-data-from-sql-server-2000-to-sql-server-2008, the script generation part is explained there.
Write a stored procedure. You'll get all user tables
SELECT o.name, o.id FROM sysobjects o WHERE o.xtype = 'U'
and write a cursor on this query.
So write an
INSERT statement dynamically while you read your cursor.
Meanwhile you browse your cursor apply a query to extract all fields about one table, in this way:
SELECT c.name FROM syscolumns c WHERE c.id = @variableIdObject
and put in a temp table the result.
So you can write the first part of an INSERT statement as the below:
INSERT(field1, field2, ..., fieldN) VALUES
This first part you'll concatenate with the data retrieve by your current table by dynamic SQL (you can create a temporary table with the upper fields name)
And at the end you can write for every rows a complete INSERT statement.
In the main temporary table, where you'll store all rows about your DB, you'll be able to execute a query to extract all your DB.
Please disabled the contraint check in the new DB.
If you want to use a semi automatically method, you can try to install Talend so you'll get your starting DB and the ending DB so you can try to create a job for every table, but in this way you must map all origin columns with all target columns.
Tell me if my answer is OK.