Restore a dba in productivity-db by merging backup and working instance

Assuming that the old DBA was fired for being a jerk and not incompetence, my first reaction is to go visit with your companies house attorney and have him draft a letter to the old DBA threatening to sue to recover damages and/or inform the authorities that he is a cyber terrorist (or whatever the equivalent European law/term is).  My guess is that this induhvidual left himself a back door, e.g. some other user login with DBA privileges and, when pressed, he will suddenly remember a login/password that will get you out of this mess.

You might look through his old stuff, scripts, utilities, tools, batch jobs, etc. that require DBA privs and see if there is one or more other accounts that appear to have some or all of the privs.

If, on the other hand, the old DBA was incompetent and/or has thrown himself under the wheels of a train out of guilt, you are probably going to have to do this via brute force.  You are also going to have to do this relatively quickly since it sounds as if you cannot backup the database right now.  I would start shutting things down for a few minutes at night and making copies of the database file(s) in place of doing a proper dump.

As you have correctly pointed out, there is no way you can manipulate all those triggers and constraints manually in a realistic amount of time.  As such, I would extract them into large scripts, remove them completely from the target database, load the data, then reapply them using those scripts.  I would also do the same thing with the indexes since you really don't want them to build incrementally.  Load the data, add the indexes, triggers, and constraints.

You don't tell us which version of SQL Anywhere you are using or how much data you have in this system so it is kind of hard to make specific recommendations on the best way to move the data.

One other thing you might consider is trying to do an upgrade to a newer version of SQL Anywhere.  It might be that the DBA privileges will get reset during that process or simply not brought forward in favor of defaults.  Kind of a long shot but might be worth a try.

Of course, you need to call Sybase tech support and see if they have any ideas.  First level support will come up empty so hold out and get it escalated to second level before you give up.  This assumes you have a supported version and a support contract.

Best of luck with this one.

Regards,
Bill

hi bill,
most of your sentences could have directly be written by my hands :). we've got the first version: there is another superuser that was actually used to take the dba's permissions - WE GUESS. officially nothing happened and nobody knows and there are no proof and so on. unfortunately - beside leaving us with at least with a working productive system - there are no administrative possibilities left at all. no backup, no dump, no maintenance and whatsoever (files are manually backed up each night right now like you mentioned).

sybase - probably first level - support is/was involved but did not come up with a better solution than the one you/i mentioned. it was only pointed out that regarding the size of the database and the number of dependencies it could be a pretty hard job.

unfortunately i'm absolutely no sybase admin, the sql-server-days a wee bit gone and my job could be named firefighter in several places.

to move the data, i'd see two different approaches:
1. drag the data from sybase to sql-server via dts, empty the sybase-target-tables and push the data back via dts (looks possible so far - forgetting the dependencies)
2. create insert-scripts for each source-table and execute them via e.g. Interactive SQL. problem: is there a possibility to automate creating those scripts?

to give an overview: we're talking about several hundred tables and a corresponding number of stored procedures and triggers. some of the tables several hundred thousand rows. no documentation or whatsoever left.

my big concern are the triggers at first hand (as i can't know exactly what they are going to do in a bad mood) and even worse the dependencies between the tables.

so assuming i could manage moving the data from source-db to target-db somehow, there's the problem how to avoid a lot of hassle with all that automatic stuff existing in the db.

two versions again that i could imagine and where your opinion would be of much help:
1. insert the data table by table in the right order hoping that nothing automatic is activated. (inappropriate in my eyes)
2. somehow deactivating all keys, triggers, dependecies and whatsoever automatic. afterwards pumping all the data into the targetdatabase and afterwards reapply all the functionality. the big question: is there a way to - for example - create scripts the automated way that reapply all the triggers and keys/dependencies? of course not manually if possible as this would take a bloody lot of time. although it would be possible at least if there is a way to install dependencies/keys afterwards via scripts.

the source of the triggers should be stored completely in the triggers and could be extracted manually into a large script that could be executed later on. but what about the dependencies/keys/indizes? do you think it's possible to extract them into a script and if so, is there a reasonable fast way?

as without proper support for the systems no big future is possible, other possibilities are quiet considerable. so no new updates or whatsoever regarding the old systems will be really taken into account - my guess.

one thing i forgot... it's not only ONE database where this problem occured... it's several ones... and somehow some people are in a quiet bad mood.
thanks in advance.
kol

so far our situation:
1.) we're able to delete and re-create the triggers and dependencies
just copy&paste and afterwards delete for each table all triggers and all direct dependencies manually into corresponding sql-scripts. perhaps there is a faster way i don't know about yet.
2.) transfer the data via an in-between-step using sql-server's dts from the current non-dba-db to the old-data-fully-functional db. 5 percent chance of wrong conversions between different database-types.
if there would be a way that activates the sybase-db to create sql-scripts that allow to reproduce the data you'd make my day.
thanks in advance.

If the powers that be know what has happened and don't take a legal approach, they pretty much deserve what they get.  Sorry it all runs down hill to you but you might want to make sure they understand that all they really need form this guy to fix all of this is one password.  Of course in my decidedly American, Clint Eastwood way of looking at things, I'm quickly coming to the conclusion that what this guy really needs is to have his hands broken.  I'm betting some soccer hooligans could beat it out of him for the price of a few pints 8-)  Ah, but I digress.

The tool you need for dealing with the scripts is Sybase PowerDesigner Physical Architect.  It is freely available on the installation disks of some versions of SQL Anywhere Studio (I think) and on the Windows Client disk for Sybase ASE.  You can download the Windows Client disk for Sybase ASE for free on the Sybase web site.  The included versions of PowerDesigner are generally a full version or two old but I am betting you are using an older version of SQL Anywhere anyway.

The nice thing about PowerDesigner is that you can point it at a database and it will reverse engineer the whole thing for you.  It creates a very ugly diagram but you don't care about that anyway.  All you want is to use the extensive and powerful script generation capabilities.  Assuming you have reverse engineered the database, you can, for instance, create a script that Drops all the triggers and another that puts them all in place.  You can do the same thing with constraints and indexes so that whole piece of the puzzle you were so worried about just went from daunting to trivial.

Moving the data is not bad either.  You can use UNLOAD TABLE  TO  FORMAT BCP to get the data out and LOAD TABLE FROM  TO  FORMAT BCP CHECK CONSTRAINTS OFF DEFAULTS OFF.  That way all you need to do is TRUNCATE the target tables, remove the triggers and indexes, load the data, then reapply the indexes and triggers.

You can script the UNLOAD TABLE and UNLOAD TABLE commands.  In fact, you can generate the scripts with a pair of SELECT statements, something like...

SELECT 'UNLOAD TABLE ' + name + ' TO ' + name + '.dat FORMAT BCP'
   FROM sysobjects
WHERE type = 'U'

You are going to want to take this off into a corner and do a few dry runs to see how long the processing actually takes.  Your bosses are going to have to be informed that the source database will be unavailable until the migration is complete.  If it takes 3 hours, that is just how long it is going to be out of commission.  The other thing they should know is that user passwords will be old.  Anyone who has changed a password since the backup you choose was take will revert to his old password.

Normally, I would be worried that you would have problems with user permissions on objects since they disappear when the objects are removed but you can get around that by pulling the user permissions for the stored procedures with their definition.  You will really grow to appreciate PowerDesigner before this whole thing is over.

BTW, I would hit up your boss(es) to actually purchase a copy of PowerDesigner.  You get support and updates that way and it will pay for its $995 price the first day.

Regards,
Bill

Believe me, acting like Clint would be a pleasure to me. The full situation involves a full-size broken down IT with a lot of servers in an absolutely chaotic fashion, no documentations, bills for paid licenses, but nothing else but the bills - and as you'll see shortly no working backups.

The backups that have been taken over years where just copies of the databasefiles. WITHOUT stopping the database in between. And right now - of course just a small problem, but not being familiar with Sybase it's stopping me right now - I'm trying to get an old backup including the DBA running. But the transaction-log is older than the db. If I shouldn't stumble over a solution, do you have an idea how to force the server to forget about the old log (I'll completely refill the tables anyway) and restart? "dbsrv10 -F databasefile" did not work as i expected it to work.

The rest of the stuff is ready to be checked out. Data is already transferred into ASCII-files via OUTPUT (no permissions for UNLOAD). Now i'm about to refill via LOAD or INPUT once the server got up again.

dbsrv10 databasefile -gl all -> give permission to anybody to load/unload

Great!  The Unload/Load should work more cleanly and faster, especially since you can use the binary BCP format.  I think I would start over that way and forget using the ASCII files.

I two other thoughts on how to get out of this mess.  The first is that, IF these databases were setup with integrated authorization, you could change the password to the alternate DBA account at the Windows level.  I know it is a big if but it is worth a try as it fixes the whole thing in 2 minutes.

The other thought I had was password guessing.  You might "attack" the alternate DBA account, and perhaps the old guy's personal account if he had one, and try stuff that makes sense.  The default is a good starting point but you should pull his personnel record and try his street name, wife's name, kids name(s) (god let's hope the bastard hasn't procreated!!!), birthdays, house numbers, make and/or model of car, hobbies, etc.  You'll probably blow an hour doing this but the professional hackers have a surprisingly high hit rate with this kind of thing.

The other attack is to write a password generator.  I think Joe Woodhouse did this at one site and, while it took several days to succeed, he did get in for his client.  The nice part about this is you set it up, let it run, and then go away until it comes back with the answer.

I don't know how to deal with the bogus log issue.  Quite frankly, you could start from scratch and build a new database with the object scripts coming from PowerDesigner in probably 20 minutes.  The only thing you might be missing are the passwords and, even then, I am pretty sure you can unload the sysusers table and stuff the encrypted versions of the passwords into the new database.  Worst case, everyone needs to set a new password.

Regards,
Bill

Hi Bill, I'm just about doing a testrun. Once i've got the proof that everything works, I'll give a report about the whole thing.

The whole story in the background is quite complicated and no big fun for the company. And as you probably already guessed I'm not Mr. Databaseadministrator himself, but as mentioned the firefighter ;). Writing the mentioned little program for a brute force job would be an easy job, but take - let's say - ten digits, allow a-z, A-Z and add all the special chars and we're pretty close to a million years of calculating in the worst possible case. We thought about that ;).

No. The approach to drop anything looking like a dependency and then just push all the data back in and add the dependencies looks like the fastest way to me (except integrated authorization - but the old admin was lazy, not stupid).

The triggers are given a little holiday by the -gf parameter. Right now there are three open questions left:
1.) while loading i just received 4 errors (translated) Load Table with ON COMMIT DELETE ROWS not permitted for temporary tables - but I'll check that once execution finished
2.) will the dependencies, indexes, keys, constraints, ... be put in place properly again?
3.) will the data have been transferred 1:1 - i'd like to go for a kind of mechanism to check that exactly if possible.

No matter how it'll end, your help was absolutely great and really appreciated!

so far everything worked. only one problem - reckon a problem of order - the foreign keys can not be reapplied. he keeps telling me he's not able to find the column he's asking for when executing ALTER TABLE ... ADD CONSTRAINT... NOT NULL FOREIGN KEY ... REFERENCES ... ON UPDATE CASCADE

i don't see the mistake at first look after 14 hours. could it be about order? i ordered this way:
1. primary keys
2. secondary keys
3. constraints
4. indexes

the triggers were not deleted and reapplied afterwards, but deactivated with the -gf-option when starting the server. and as far as i know, truncate and load don't fire the triggers anyway. could there be a problem with something like an auto-commit? i forgot about that. would be a good idea to deactivate that while loading. do you have any idea what could be the problem?

thanks in advance.

The whole database-design is quiet inconsequent in this case - like the rest of the former IT. Starting with stored procedures in the database, which - consequentially applied - would allow you to have a high security solution by having only productive users with permissions to access the stored procedures and no database-relevant functionality in the following layer of the software outside the database (forgetting about pros and cons of flexibility of such a solution). But here you'll find a mixture of stored procedures and functionality in the resource-access-layer of the software. But even worse: The software is working with the DBA-user which was fully opperational all the time. Even some external websites accessing the database for reading-purposes contain the DBA-login plus password. Believe me, the full scenario is not only an absolutely fullsize mess, but a catastrophy. And no - I won't tell anyone the URL of the company... ;)

Some of the foreign keys - of course including the ones responsible for all the CASCADING-stuff - could not be reapplied so far. But as i can't know how much the software relies on the effects of those cascaded stuff, I've got to make sure, everything's working.

For me there are two obvious ways:
1.) re-apply the deleted keys automated or manually - perhaps in the right order. if that should work - we're talking about thousands of them - there's the second solution left
2.) forget about the keys and dependencies and just leave them there, but deactivate the triggers -> -gf. find out the order how each table has to be loaded again to meet the requirements of the dependencies.

I'd like to forget the 2nd version as this is not possible the automated way and a weekend should not be enough to restore the databases this way. And I'd reckon closing the company for a week shouldn't be an option at all.

DELETE
1. Index
2. Foreign
3. Unique
4. Primary

RE-CREATE
1. Primary
2. Unique
3. Foreign
4. Index

Was obvious, but too late last night. This way the dependencies can be reapplied. Taking the hopefully first full testrun now.

Bill, thanks a lot. Your help was great and really much appreciated!

Problem:
----------
The permissions of the DBA shall be restored. So a backup is taken - several weeks old. The data from the productivity-db is exported and importet into this old backup. Wether it's the best or appropriate way, I don't know, but it's working.

Solution:
----------
STEP 1 - EXPORT DATA FROM PRODUCTIVITY-DB

1. Start Server with param "-gl all" (unload permission for all)
2. Generate unload-scripts and unload DB
2.1 Run following script, export results to a new script (*.txt) and run that script

SELECT 'UNLOAD TABLE ' + name + ' TO ''c:\\temp\\' + name + '.dat'' FORMAT BCP;'
  FROM sysobjects
WHERE   type = 'U'
AND     uid  = 1  -- in my case the owner of the tables
ORDER BY name

STEP 2 - LOAD DATA INTO TARGET-DB

1. Start Server with param "-gf" (deactivate triggers)
2. Remove all dependencies and create script to reapply them later on
2.1 Create a script (reapplyDependencies.sql) with the following content (paste the copied elements under the comments later on):
-- Primary Keys

-- Unique Constraints

-- Foreign Keys

-- Index

2.2 Open Sybase Central - Indexes - sort by indextype
2.2.1 Select all INDEXES, cut them and paste them into the script
2.2.2 Select all FOREIGN KEYS, cut them and paste them into the script
2.2.3 Select all UNIQUE CONSTRAINTS, cut them and paste them into the script
2.2.4 Select all PRIMARY KEYS, cut them and paste them into the script

3. Close Sybase Central, open ISQL (avoid table-locks!)

4. Generate truncate-scripts and truncate tables (could be combined with load-script, but gives you possibility to find out where the script stopped in case of error)
4.1 Run following script, export results to a new script (*.txt) and run that script

SELECT
        'TRUNCATE TABLE ' + name +';'
FROM    sysobjects
WHERE   type = 'U'
AND     uid  = 1
ORDER BY name

5. Generate reload-scripts and reload the data
5.1 Run following script, export results to a new script (*.txt) and run that script

SELECT
        'LOAD TABLE '+ name + ' FROM ''c:\\temp\\'+ name + '.dat'';'
FROM    sysobjects
WHERE   type = 'U'
AND     uid  = 1
ORDER BY name

6. Reapply the dependencies by executing the reapplyDependencies.sql mentioned in 2.1

Now - in case of success - grab a beer. Else have a stroll into the mountains...

Thanks a lot, Bill. Help was really appreciated.

(DBs will be restored next weekend while a completely new infrastructure is set up)

Congratulations.  It looks like you have a way out.

And thanks for taking the time to write down the detailed solution.  It makes this whole thing very much more useful for the Experts-Exchange audience.

Regards,
Bill