I have 2 database environments. Test and Production.
Both environments have Identical table names and procdedure names.
Test differs from production in that some of the table structires have additional columns etc, also Test has more procedures.
If I drop just the tables in Test (I'll use Erwin to redo the table structure changes) and then load the database dump in production, Will ASE ignore all objects with the same name ala procs and users and just load up the tables?
A database dump is just that. It's the whole database; tables, triggers views, procedures, whatever. If you drop the tables and dump the database, you will have is a database with no tables in it together with whatever procedures were in there. Load it on to another server and it will over write the contents of the database on that server. Just because there are no tables in a database dump, doesn't mean ASE will preserve the existing tables.
Drop the tables from your Test environment
Dump the database in Test
Load the dumped Test database into the Production environment
Look for Production data - It's not there.
I think you misunderstood what I was trying to say.
I am actually trying to load the dump from production to test.
I guess what you are saying is if I drop the tables in Test and then Load the production dump onto Test I will get the tables from production to appear on test, but I will also overwrite all the procedures and users that exist on Test with the users etc from production
1. I would write a script containing your table differences in test.
2. Any procs that will get overwrittten in test, by a load from a production dump, should be extracted from test prior to the load.
3. create an sp_adduser script for those users in test and not in production. Be careful that suid's from production are not overlapped with test's suids.
after the production dump has been loaded into test, run the 3 scripts above.
>> I think you misunderstood what I was trying to say.
Yes I did, I stand corrected!
>> I am actually trying to load the dump from production to test.
In that case just loading a dump from production will totally replace the database in Test. There is no need to drop the tables as it will be a complete copy of the production environment.
There are a number of possible solutions to this.
1) Use BCP to copy the data from production to test
Truncate the test env. tables and use BCP to extract the data from production and load on test. This can be done easily if the extra columns are "not null" or have defaults.
2) Extract all the objects (Stored procs, triggers, views - basically anything you want to keep) from the test environment. Load the production database on the Test env. and then re-apply the objects you scripted off. Be careful to only apply the differences on the table definitions using alter table. Drop followed by create will remove the data as well. Depending on how many objects this method may take some time to complete.
3) Use SQL Backtrack
I have heard of, but never used a product called SQL Backtrack. It is supposed to be able to back up parts of a database. How much mileage you'll get with this I don't know. Maybe someone else can give a pointer on that.