Rebuilding the master Database

The master database is the system database that keeps track of user accounts, remote user accounts, remote servers, environment variables, system error messages, system databases, database storage allocation, devices, and active locks.

The setup program can be used to rebuild the master database. This rewrites the master database and resets the configuration values on its existing device. This process can also be used to change the server's character set or sort order.

Caution Rebuild the master database only when it is absolutely essential. Rebuilding the master database completely rewrites the master database, destroys all information added since SQL Server was first installed, and renders all other databases unusable. If you rebuild the master database, you must recreate the user databases, reload each database from the most recent dump, and then reapply any changes not included in the dump.

When rebuilding the master database, you have the option of changing the character set or sort order. Because the sort order is used to construct all indexes, including system indexes, all databases must be rebuilt if you change the sort order. The setup program rebuilds the master database for you, but you must rebuild user databases yourself.

If you must rebuild the master database or change the character set or sort order, follow this procedure.

    To use setup to rebuild the master database
  1. If you are changing the character set or the sort order, back up all databases. If not, go to step 4.
  2. Put all data definition language (DDL) commands in batch files. You will use the batch files to recreate the structure of your databases after changing the sort order. (SQL Enterprise Manager can help with this step.)
  3. Use the bcp (bulk copy) utility to copy data out of your tables.

    Include all database objects. Also copy out lists of users.

  4. Back up the entire SQL Server directory tree and any disk devices that you have created in other directories.
  5. From the Microsoft SQL Server 6.0 program group, double-click the SQL Setup icon and follow the on-screen instructions until the Microsoft SQL Server 6.0 Options dialog box appears.

  6. Select the Rebuild Master Database option, and then choose Continue.

    A confirmation dialog box appears.

  7. Choose Resume.

    The Rebuild Options dialog box appears.

  8. To change the character set during the rebuild, choose the Sets button, and complete the Select Character Set dialog box that appears.

    For information about the character sets provided with SQL Server, see Microsoft SQL Server Setup.

  9. To change the sort order during the rebuild, choose the Orders button, and complete the Select Sort Order dialog box that appears.

    Important The sort order is used to construct all indexes, including system indexes. If you change the sort order, all databases must be rebuilt. The setup program rebuilds the master database for you, but you must rebuild user databases yourself.

    For information about the sort orders provided with SQL Server, see Microsoft SQL Server Setup.

  10. In the Rebuild Options dialog box, choose Continue.

    The SQL Server Installation Path dialog box appears.

  11. If it is not correctly displayed in the dialog box, enter the location of the existing SQL Server installation, and then choose Continue.

    The Rebuild Master Device dialog box appears.

  12. If it is not correctly displayed in the dialog box, enter the location and name of the existing MASTER device. Also enter a MASTER device size, and then choose Continue.

    The setup program rebuilds the master database.

  13. When the rebuild completes and the completion dialog box appears, choose the Exit button.
  14. If you changed the character set or sort order, recreate all databases using the batch files created in step 2, and then use bcp to copy the data back into your databases.

For information about bcp, see Chapter 11, Exporting and Importing Data.

The files MASTER.DA@ and MASTER.AL@ are stored in the \SQL60\INSTALL directory. When installing SQL Server or rebuilding the master database, one of these two files is used. When the default sort order and character set are selected during an installation or a master database rebuild, MASTER.DA@ is expanded and copied onto the server, replacing MASTER.DAT. When an alternate character set and/or sort order is selected, MASTER.AL@ is expanded and copied onto the server, and several SQL scripts are run. Because of this, selecting the default character set and sort order results in a rapid master database rebuild. Choosing an alternate character set or sort order can result in a somewhat longer rebuild time.

Because of the introduction of one-way encrypted passwords with SQL Server 6.0, changing the sort order has an impact on the use of passwords. For information, see Microsoft SQL Server Setup.