I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.

The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.

The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.

The databases are both running Server 2000 std edition on Windows 2003 std.

Any help or guidance would be much appreciated.

TIA,
Matt Brooke