Restoring the SQL master database

If the master database is damaged, symptoms may include the following:

If you can still start SQL, you can restore the latest copy of the master database backup using the Automate master database restore option in Backup Exec's Restore Job Properties for SQL dialog box and then restore any other databases, if needed.

If the master database is critically damaged and SQL cannot be started, rather than running the Rebuild Master utility, or reinstalling SQL to be able to restart SQL, you can replace the corrupted or missing databases with the copies of the master and model databases that Backup Exec automatically creates and updates whenever backups of those databases are run. After SQL is running again, you can restore the latest copy of the master database using Backup Exec's Automate master database restore option, and then restore any other databases, if needed.

If copies of the master and model databases were not made, then you must use Microsoft's rebuildm.exe utility to rebuild the master database and start SQL.

Because all changes made to the master database after the last backup was created are lost when the backup is restored, the changes must be reapplied. If any user databases were created after the master database was backed up, those databases cannot be accessed until the databases are restored from backups or reattached to SQL.

To restart SQL using database copies

  1. Verify that the database copies are present.

    The database copies are named master$4idr, mastlog$4idr, model$4idr, and modellog$4idr and are found in the following locations:

    In a default installation of SQL 2000, the databases are in:

    C:\Program Files\Microsoft SQL Server\MSSQL\Data\*.*

    In a named instance of SQL 2000, the databases are in:

    C:\Program Files\Microsoft SQL Server\MSSQL$Instance_Name\Data\*.*

    In the first installation of SQL 2005, the databases are in:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\*.*

    In a second installed instance of SQL 2005, the databases are in:

    C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\*.*

    In a default installation of SQL 7.0, the databases are in:

    C:\MSSQL7\Data

    In a default installation of SQL 2008, the databases are in:

    C:\Program Files\Microsoft SQL Server\MSSQL10.<instance name>\MSSQL\Data

    If necessary, restore the master and model database copies from a backup set to the same directory that the original master and model databases are in.

  2. Using the Windows Explorer, browse to the default data directory and delete the following files:

    • master.mdf
      
    • mastlog.ldf
      
    • model.mdf
      
    • modellog.ldf.
      
  3. Open a command prompt window, and delete the original master and model databases and their transaction logs.

  4. Rename the copies of the databases back to their original names.

    The database names are as follows:

    Copied database name

    Original database name

    master$4idr

    master.mdf

    master$4idr

    mastlog.ldf

    model$4idr

    model.mdf

    modellog$4idr

    modellog.ldf

    Do not use read-only files. The SQL services will not start with read-only files.

  5. If you are using SQL 2000 or SQL 2005, use the SQL Service Control Manager to start SQL Server. If you are using SQL 7.0, use the SQL Server Service Manager to start SQL.

  6. Continue with the next procedure to restore the latest changes to the master database.

To restore the master database

  1. On the navigation bar, click the arrow next to Restore.

  2. Click New Restore Job.

  3. On the Properties pane, under Source, click Selections.

  4. On the restore selections list, select the backup set containing the last master database backup.

  5. On the Properties pane, under Settings, click Microsoft SQL.

  6. On the Restore Job Properties for SQL dialog box, select Automate master database restore.

    All existing users are logged off, and SQL Server is put into single-user mode.

    When this option is selected, only the master database can be restored; if this option is selected for any other database, those jobs will fail.

    If Backup Exec does not have access to the SQL registry keys HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server and HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer, then a restore to the default directory may not work, and the option Automate master database restore on the restore job properties for SQL will not work. To ensure that Backup Exec has access rights, verify that the account that Backup Exec uses has administrator rights to the computer that is running SQL.

  7. Select a consistency check to be run after the restore.

  8. Start the restore job.

    After the restore, SQL restarts in multi-user mode.

More Information

Restoring data by setting job properties