Unanswered: Data in log devices & log in data devices
I have just had the unpleasent task of restoring a Sybase database after a messy disaster with no backup scripts or much idea of what status the dump was in that I was restoring.
It is all running now, but for future reference I was wondering if there was anything else that could have been done.
I did this process a number of times
The release is
Sybase SQL Server/220.127.116.11/SWR 7926 Rollup
The problem was that I knew how how much data and how much log was on the server originally for mydatabase but not the order the devices had been created. The dump of mydatabase was from the database before the crash but they were unsure if the db had log and data mixed on the same devices.
First I ran the disk init statements and created the 15 1Gb data devices and the 3 1Gb . Then the create database and alter statements.
I checked mydatabase with a sp_helpdb mydatabase and all the log and data devices were created fine with log and data seperated.
Then I did the load and waited.
When it was complete I ran another sp_helpdb mydatabase only to find a number of the devices split up and log had been swapped for data and data in the log.
After searching and contacting Sybase tech support there did not seem to be a way to keep the log off the data devices and vice versa after the load was done. We tryed allocating the syslogs object to a device before the load and used sp_logdevice. Nothing seemed to work. I assume if I had the full details of device creation then this would not have been such a headache.
As I said I ended up reverse engineering the db and objects using revsql and bcp'd out all the data using perl scripts and then loaded it into the new db. Thus reducing the dump from 12Gb to 6Gb!!
Two questions really. Is there a way of forcing the data and log to load to the correct devices before loading a database which has been dumped from an instance of mixed data and log?
If data and log become mixed again on the same devices, what is the best course of action?