0

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?

Improve this question
4
  • Create a Linked Server between SQL Server 2014 and SQL Server 2000. Then INSERT using a SELECT. technet.microsoft.com/en-us/library/aa213778(v=sql.80).aspx – Mark Sinkinson Sep 19 '14 at 14:43
  • Or if you have other SQl server r like SQL server 2008/2008R2/2012 you can do backup and restore . First restore on 2008/2008r2 or 2012 and then take backup again and restore on 2014. I know you said you need to migrate only data but this would give you all data,structure,keys constraint every thing. – Shanky Sep 19 '14 at 14:47
  • There is a SELECT who makes copy whole data base? Only one table at a time? – eduard Sep 19 '14 at 14:52
  • 1
    @eduard Only one table at a time. Though you can create a script to copy the contents of every table, one at a time. (But I also think you actually want to restore the database.) – RLF Sep 19 '14 at 15:33
3

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

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.

Improve this answer
0
1

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.

Improve this answer
4
  • There is no Advanced options in a SQL Server 2000... – eduard Sep 19 '14 at 15:32
  • 1
    This is how to do it in SSMS. – user507 Sep 19 '14 at 15:49
  • @eduard I have edited my answer. – vijayp Sep 19 '14 at 16:52
  • @vijayp Thank you. I solved that using the solution bring by RLF. – eduard Sep 22 '14 at 15:43
1

Try this:

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.