Recovering SQL manually

If necessary, restore the Windows computer.

See Manual disaster recovery of Windows computers.

After recovery of the Windows computer is complete, or after the new server installation is available, recovery of the SQL databases can begin.

In order to restore SQL databases, SQL must be running; however, SQL cannot be started unless the master and model databases are present.

You can restore the master and model databases and start SQL using one of the following methods:

This topic only details how to restart SQL by using the copies of the master and model databases made by Backup Exec. For more information on the Rebuild Master utility, or on reinstalling SQL, refer to your MS SQL documentation.

If you are restoring to a new SQL installation, start with the restore of the master database.

See “To restore the master database”.

To restart SQL 2000, SQL 2005, or SQL 2008 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:

    A default installation of SQL 2000

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

    A named instance of SQL 2000

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

    An initial installation of SQL 2005/2008

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

    A second installed instance of SQL 2005/2008

    C:\Program Files\Microsoft SQL Server\MSSQL.2\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. 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.

  4. Use the SQL Service Control Manager to start SQL Server.

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

To restart SQL 7.0 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.

    In a default installation of SQL 7.0, the databases are in C:\MSSQL7\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. 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.

  4. Use the SQL Server Service Manager to start SQL.

  5. 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 run after the restore.

  8. Start the restore job.

    After the restore, SQL is restarted in multi-user mode.

  9. Continue with the next procedure, restoring the remaining SQL databases.

To restore the remaining SQL databases

  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. Select all the backup sets that you want to apply, including the full backup, any differential backups, and any log backups.

    Do not select the master database for restore at this time.

    If you are restoring the SQL databases from filegroup backups, you should be aware of the applicable conditions.

    See Restoring from SQL filegroup backups .

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

  6. Click Leave the database ready to use; additional transaction logs or differential backups cannot be restored.

  7. Click Overwrite the existing database.

  8. In the Consistency Check After Database Restore field, click Full check, including indexes.

  9. Start the restore job or select other options from the Properties pane.

    See Redirecting restores for SQL.

    When all of the restore operations have completed successfully, then the recovery of the SQL databases is complete.

    After the recovery has been completed, Symantec strongly recommends that a full database backup be performed as soon as possible.

More Information

Restoring data by setting job properties