Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Question Unanswered: loading tables in Sybase

    Quick Question:

    Scenario:
    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.

    Question:
    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?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: loading tables in Sybase

    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.

    Consider this...

    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.

  3. #3
    Join Date
    Mar 2002
    Posts
    162
    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

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Yes, that's correct. When you load a database from a dump you replace whatever existed in that database previously.

    In your case you may need to find a diffferent method of getting recent production data into your test database, possibly by using bcp.

    Michael

  5. #5
    Join Date
    Jan 2003
    Posts
    28

    Re: loading tables in Sybase

    I sure hope that was a typo. Never take a test db and load it into production.

  6. #6
    Join Date
    Jan 2003
    Posts
    28
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    >> 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •