I have done this several times on a project that I took over recently.
I was using mySQL but it doesn’t matter.
I was using toad edge.
And it had a way to export data with table INSERT commands, so you could just run that SQL and it would execute The INSERT statements to load data.
Then I would load into text editor, and do one global change:
basdb.crm_custs to basdbtest.bastestdomaincrm_custs
Find/replace “basdb.crm” to “basdbtest.bastestdomaincrm” ( I may not have those names exactly correct, but forgive me I’m doing this away from my computer ) and voila.
This is going to change the field on the INSERT statement, so it’s inserting the data into the testdatabase.tablename.
You’ll see that there’s one line for each INSERT statement. None of the data gets changed.
Then run the SQL and all that live data is now in TEST
The only problem I had was with binary/blob data.
Fortunately, toad edge had a way to eliminate certain data types from the export.
So I can eliminate binary, tiny blob, medium blob, long blob and know that they would NOT go out In the export.
SSMS has a way to do a right click and generate SQL INSERT statements also, but I’m not sure if you could eliminate specific fields.
( The first time I did this using mySQL workbench, I had massive problems because the INSERT failed because of all the binary data.
So I have to search the application, which was new to me, and find where these binary/blob fields were coming from and eliminate those tables.
Then when I started using toad edge I found it had a nifty way to eliminate them at the field level)
It only affected me in one place with a signature field in a user file, and of course in the pictures/photos file, which I really didn’t need over the test system anyway
Jaymer
PS
Of course you don’t export the system tables, and you can skip the UDP tables which are for user-defined system functions.
You definitely need to make sure you have the next ID number matching LIVE.
