I have almost exactly same problem, so will use this topic, too 🙂

I've got a large (1TB) database on SQL Server 2000 and need to move "archive" data to a different server (also SQL Srv 2000).

I do not have currently any archiving mechanisms on the source server (except of daily backups), so I wonder, what will be the best option:

1. replicate the data needed to new server and delete from the source via horizontal/vertical replication with SQL Server replication mechanisms

2. using DTS (I'm not very familiar with DTS)

3. write my own T-SQL query to insert data to newer server and to delete from old one

Only few tables are to be archived and only a certain list of columns is needed to keep.

Which option would be best to consider to?