In computer systems, yes, every error message matters, it is trying to tell you something is wrong; you did not get the results you attempted to obtain.
Well, did you create the master database with 'for load' ? If so, you cannot do that. If not then there is something else wrong, and you have to chase that down.
It may help if you read the manuals and get your head around what you are trying to do. Have you got:
- a working (not broken) server
- a damaged master db, but not a damaged master device
If so, then, without creating the master db, using the master db that exists:
- start ASE in single user mode (as you have done)
- load master db
post any and all error msgs you encounter.
Thanks for the reply. I was trying to restore my databases back to what they were 2 days ago on the same machine. All the user defined database loaded fine but 'master' did not. I was not the original creater or the 'master' database so I don't know if it was created for load. We have sybase 18.104.22.168 running on window server 2003 and here's the entire error message I've gotten. If you look at the details, 'master' did load and it looks like everything is working fine (ex: all the newer logins are removed and sysdevices and sysusages are reporting the right info), but I still want to find out why that error came up :
1> load database master from "D:\master_091609.dmp"
WARNING: In order to LOAD the master database, the SQL Server must run in
single-user mode. If the master database dump uses multiple volumes, you mus
execute sp_volchanged on another SQL Server at LOAD time in order to signal
Backup Server session id is: 17. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 22.214.171.124: Dumpfile name 'master092590E676 ' section number 1
mounted on disk file 'D:\master_091609.dmp'
Backup Server: 126.96.36.199: Database master: 2004 kilobytes LOADed.
Backup Server: 188.8.131.52: Database master: 6150 kilobytes LOADed.
Backup Server: 184.108.40.206: Database master: 6158 kilobytes LOADed.
Backup Server: 220.127.116.11: LOAD is complete (database master).
Redo pass of recovery has processed 1 committed and 0 aborted transactions.
(5 rows affected)
Msg 930, Level 14, State 1:
Database 'master' cannot be opened because either an earlier system terminati
left LOAD DATABASE incomplete or the database is created with 'for load' opti
Load the database or contact a user with System Administrator (SA) role.
ct_results(): network packet layer: internal net library error: Net-L
ary operation terminated due to disconnect
The sybase ase version is 18.104.22.168 runing on windows, the load finished ok but here are the additional messages in the backup log file:
Sep 16 16:30:36 2009: Backup Server: 22.214.171.124: LOAD is complete (database master).
Sep 16 16:30:36 2009: Open Server Error: 16144.10.0: Server process '14' is not allocated in 'srv__prepare_for_suspend'
Sep 16 16:30:36 2009: Open Server Error: 16144.10.0: Server process '14' is not allocated in 'srv__prepare_for_resume'
Ok. For understanding: X is the set of backups (db dumps) you want to restore to.
Y is the point you were at, 2 days forward of X, when you decided to restore to X.
1 If you wanted to restore the server to X: you need to load all the databases except master. You can do that without placing the server in sungle user mode (-m in either the startserver or dataserver command). This means you keep the logins and devices as of Y.
2 The only reason to load master is when the master database is actually damaged. To load master, you need single user mode. And of course, you lose logins, devices, user dbs that were created since the dump_file you are laoding.
Z is the first time you tried load master which failed.
Now the problem is you are retrying it, and the second and subsequent time, you are getting this error (Database 'master' cannot be opened because either an earlier system termination left LOAD DATABASE incomplete or the database is created with 'for load' ).
3 Since you have not recreated master, the second half of that err msg does not apply. The first half applies. It is true, the previous (first) load master attempt was incomplete).
4 So go back to Z. What was the err msg in your isql session and in the errorlog, for the first time you tried load master (the second and subsequent times are not relevant) ? Or did you just forget to online master ?
5 You did not have a damaged master at X or Y. But now that you have an incomplete load master (the current err msg), now you do have a damaged master.
To recover you need to do the following. First understand that you do not want to damage the server further, so that you have to rebuild a new server; what you need to do is recover the server "in place". Understand the following steps before you start (ask questions, get clarification, etc); Do not proceed to the next step until the previous step is successful. Use a separate startserver file, and add the switches required:
6 Start the server in single-user mode, and instruct it to overwrite the master device (with a new "simple, generic" master db, that will allow eg. procs to be executed, but no devices or user dbs):
dataserver ... -m -w
7 Load master db from your dump_file. Online database master. Nnw you have your master db, including devices, dbs, logins. That will bring master, and the server (but not the user dbs) up to X. The user dbs are in a questionable state. Checkpoint. Shutdown.
8 Restart the server as normal, without single-user mode.
9 Load and Online each user database in turn (RI sequence). That will bring them up to X. As each db is onlined, it will become available.
10 No need to load system database unless they were damaged. Check via exec sproc, etc.
Our original intent for load the master database is to bring our database server back to 2 days ago without the newly added logins and device info so we can go back to a particular stage of our server which was 2 days ago.
It was during the first time of loading the master database that 'for load' error came up and so did the second time.
It did not ask me to issue 'online database master' after the load, all it did say in the sybase backup log is "load master database complete but it had errors'.
All the other user defined databases loaded fine without errors and I did issue 'online database' for each of them.
Even with that 'for load' error in the first time 'load database master' command, our database server seems to be working without any errors. Is there a way to find out whether the 'master' is actually damaged? Is there a documented 'DBCC' command for fixing the 'master'?
We do have a very old version of Sybase, it is 126.96.36.199, maybe this error it is fixed in the later version. Do you know which version that is exactly? 15 or 12.5.1?
Even with that 'for load' error in the first time 'load database master' command
Read again, the msgs says Database 'master' cannot be opened because either an earlier system termination left LOAD DATABASE incomplete or the database is created with 'for load' option. Since 'for load' does not apply, the 'load incomplete' applies, that's what you need to hang your hat on. As per standard practice, while the every error message matters remains true, and we have to chase them down: in this case the error message itself is the error, not the 'load master'.
The bug is the erroneous 'load incomplete' error msg being reported on the first 'load master'; this is now a spurious issue, since you seem to be up and running just fine.
our database server seems to be working without any errors. Is there a way to find out whether the 'master' is actually damaged? Is there a documented 'DBCC' command for fixing the 'master'?
Yes, just run normal dbcc on master. (You should be running dbcc on all databases, including master and the other system dbs, at least twice a week, if not every night.) Normal dbcc, as in do not use the "fix" option. If and when dbcc reports a hard error, then yes, use the "fix" option, to correct just the single damaged table or index.
Looking into CRs (bugs fixed) across that many versions is a substantial piece of work.
188.8.131.52 is old, but not that old. The question is whether you were up-to-date with EBF/ESDs within that version, when it was being maintained. Recovery of the server, particularly loading master, is heavily tested, there is no way such as bug would not have been found and fixed. In any case, 184.108.40.206 was End Of Lifed a few years ago, so there will be no further bug-fixes.
I do not recommend 15.0 for production.
If you can, go to 12.5.4, but not just to get over that bug; it has many more features than 220.127.116.11, it is very stable, and it is much faster. It's End Of Life is scheduled for 31 Dec 09, so I would download it and get used to it while I can.
Last edited by Derek Asirvadem; 09-20-09 at 09:11.
Thanks! I will look into 12.5.4 for sure.
By the way, if I want to take my dump files to a new server, what is the fastest and safest way to load them? Let's say the new server already has a sybase installation on it but the logins, sysdevices and drive configurations are all different than what's in the dump files; is it easier just to remove the sybase installation all together on the new server and build an empty shell (just with the system databases) then build the user define databases from the 'disk init' scripts saved from the old machine?
If everything is successful then load the dumps, this way none of the syslogins, syssrvroles, sysalias, sysdevices, sysloginroles, sysdatabases info will be lost, isn't it? Please advice....
Loading a database from another server is clean and easy, but there are server environment issues that need to be understood and resolved. "Proper planning prevents poor performance."
1 There is no need to confine the new server to the physical setup of the old server. You can create it independently:
- to take advantage of the new hardware
- set up new, different Devices (bigger, fewer) based on what they are going to contain in the long term
- certainly set up the system databases correctly for the intended use
2 If you are going to dump db from old_server, load db into new_server, then you need to resolve the differenece (delta) between the Logins on the two servers (otherwise the result is mismatched Logins, mismatched Users & Aliases, and broken security; followed by manual labour to fix that up). There are two methods, choose one xor the other and follow it all the way through (a combination does not work):
a. Fixup After Load (Every Time)
Load db into new_server, write scripts to drop all Users & Aliases (within the db) and add the correct Users & Aliases, based on new_server_Logins. Essentially you have two separated security environments. I do not like that.
b. Synchronise Logins (Once, and Keep Them Synchronised)
b.1 when you set up the new server, drop all Logins (except 'sa', 'probe', etc)
b.2 bcp-out old_server.master..syslogins (exclude 'sa', probe, etc); syssrvroles; sysloginroles.
-- Basically Logins and Roles
-- not Databases, Users, Aliases:
---- Databases will be created as you need them
---- Users & Aliases are contained in the Database, they travel with the dump_file.
b.3 bcp-in new_server.master..syslogins; syssrvroles; sysloginroles. You are now ready for uneventful load db (Users & Aliases intact).
b.4 The Logins may be locked on one server and not the other (eg. developers locked on Prod; ordinary users locked on Dev).
b.5 Remember, every time you add a Login to one server, you must add it to the other. That is easily scripted.
3 Load Database from another server
This is simple and uneventful if you have the exact same Allocations for the database (same chronological order as it was created and altered on the source_server, where you perform the dump database); otherwise you will get mixed data/log and recovery will be crippled, and very hard to fix up.
a. (if you have mixed data/log on the source server, you will need to correct that first.)
b. Grab the code in this post, change DB_NAME, and execute on your source server.
c. Then change DATA|LOG|MIXED_DEVICE to identify Device names on the target server, and exec that on the target server.
d. Then dump db on source_server; load db on target_server
e. You should have a clean, uneventful load, all Users & Aliases intact, no mixed data/log.
Again, it is better to understand these issues and plan to prevent or minimise them:
- do not add Allocations to your databases bit by bit; plan the Device and Allocations, and add them so that there are less, rather than more Allocations
- Along with the nightly dumps, I always write [2.b.2] and [3.b] to text files, and have them included in the o/s backup (tar volume): this allows me to recreate a server manually and load dbs, without any loss. It is always there for reference, if the synchronisation breaks, to re-synch yourself.