How to move the master database to a new device

Posted on by

This error occurs when you try to extend the master database onto a device other than the master device.

It is recommended that you keep user objects out of the master database. If you keep user databases off the master device, you allow space in case the master database needs to grow. In addition, if you ever need to rebuild the master device, it will be easier if it does not contain user databases.

Adaptive Server users can move any “home-grown” system procedures that start with “sp_” to sybsystemprocs (by dropping them from the master database and creating them in sybsystemprocs).

Extend the master database only if absolutely necessary! If you are sure you must increase the master database size and have no room on the current master device, use the following procedure to remove user databases from the master device.

Move User Databases

  • Dump the user databases with the dump database command.
  • Rename the dumped databases on the master device with sp_renamedb.
  • Re-create the databases with their original names on another device with create database. Be sure they are created exactly like the old databases, to avoid 2558 and other errors. Refer to Error 2558 for more information.
  • Load the dumps with load database.
  • Use the online database command for each database to make the databases available for use.
  • Check the databases in their new location to make sure the load was successful (that is, perform a simple query with isql), and if everything loaded successfully, drop the old databases from the master device.

You can now try to increase the size of the master database on the master device with the alter database command.

Increase Master Device Size

If the master device contains only the master database and the master device is too small, then use the following procedure:

Warning!

Altering the master device is extremely risky! Avoid it if at all possible. Be familiar with the recovery methods in “System Database Recovery” in case you lose your master database or master device.

  • Back up the master database with the dump database command.
  • Save the contents of key system tables such as sysdatabases, sysdevices, sysusages, and syslogins.  Make a note of these values.  Also make a note of the path to the dump device in sysdevices.
  • Use the buildmaster utility to build a new master device with enough extra space so that you will never need to increase the master device again. When buildmaster completes, a new master database will exist on the new master device. The buildmaster executable is found in bin, so use ./buildmaster and follow the prompts.
  • You now need to create a new runserver file which points to this new master device (the -d option). And start up the server with this new runserver file.
  • Expand the size of the new master database with the alter database command, if necessary, so that it matches the size of the dumped master database(get this info from the original sysusages table where the size is in 2k blocks, the alter database command uses sizes in MB).
  • Execute the following command in isql:

1> select name, high from master..sysdevices

2> where name = “master”

3> go

and note the “high” value for the master device. Shutdown the server.

  • Add the –m option to the runserver file to start Adaptive Server in single-user mode.
  • Allow updates to the system catalog:

1> sp_configure “allow updates”, 1

2> go

  • Change the value for srvnetname in sysservers from SYB_BACKUP to the name of your backup server.
  • Load the dump of the master database, using load database master from <full path name>.
  • Reset the “high” value in master..sysdevices:

1> begin transaction

2> go

1> update master..sysdevices

2> set high = <value of high from step 5>

3> where name = “master”

4> go

  • If the previous update affected only one row, commit the transaction.
  • Restart Adaptive Server.
  • Turn off allow updates:

1> sp_configure, “allow updates”, 0

2> go

  • Edit the new runserver file to take it out of single user mode, i.e remove the –m option and restart the server, if this all works fine (leave it for a while) then you can remove the original master device and its related run server file.