We use cookies on our website to ensure we provide you with the best experience on our website. By using our website, you agree to the use of cookies for analytics and personalized content.This website uses cookies. More Information
It seems like your browser didn't download the required fonts. Please revise your security settings and try again.
Barracuda Yosemite Server Backup

Restoring Microsoft SQL Server 2000 Master Database

  • Last updated on

A damaged master database is evident by the failure of the SQL Server to start, by segmentation faults or input/output errors or by a report from DBCC. An example of an error might be damage caused by media failure in the area in which master database is stored.

The procedure used to recover a damaged master database is different from the procedure used to recover user databases. If the master database becomes unusable, it must be restored from a previous dump. All changes made to the master database after the last dump are lost when the dump is reloaded and therefore must be reapplied.

It is strongly recommended that the master database be backed up each time it is changed. This is best accomplished by prohibiting the creation of user-defined objects in the master database and by being aware of the statements and system procedures, and the equivalent actions in SQL Enterprise Manager, that modify it.

The most common statements and system procedures that modify the master are:

  • CREATE DATABASE

  • ALTER DATABASE

  • sp_dropremotelogin

  • sp_addumpdevice

  • sp_dropdevice

  • sp_addlogin

  • sp_droplogin

  • sp_addserver

  • sp_dropserver

  • sp_addremotelogin

If a user database is created, expanded, or shrunk after the most recent dump (backup) of the master database and if it becomes necessary to reload the master database, then that user database and all data in it will be lost and must be restored from backup. Because of this, always dump (back up) the master database after creating, expanding, or shrinking user databases.

Complete the following steps to recover a damaged master database:

Step 1. Stop Yosemite Server Backup and SQL Server Services

Exit Yosemite Server Backup, and stop the Yosemite Server Backup service by using one of the following methods.

Method 1. Windows Command Line

  1. At a command prompt, change to the following directory:
    C:\Program Files\Barracuda\Yosemite Server Backup
  2. Type the following command at the command prompt:
    ytwinsvc -x  

The Yosemite Server Backup service stops on the local machine.

Method 2. Microsoft Management Console (MMC)

  1. Right-click My Computer, and then click Manage. 
  2. In the left pane, click Services and Applications Services. 
  3. In the right pane, locate the Yosemite Server Backup service, right-click the service, and then click Stop.

  4. Stop the SQL Server service using the SQL Server Enterprise Manager.

Step 2. Rebuild the Master Database

  1. At a command prompt, enter the following command to switch the directory:
  2. Program Files\Microsoft SQL Server\80\Tools\Binn
  3. Run:
    Rebuildm.exe
  4. In the Rebuild Master dialog box, click Browse. 
  5. In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK. 
  6. Click Settings.  In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
  7. Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK. 
  8. In the Rebuild Master dialog box, click Rebuild to start the process.

    To continue, you may need to stop a server that is running. The Rebuild Master utility reinstalls the master database.

     

Step 3. Restart SQL Server in Single-User Mode

  1. At a command prompt, enter the following command to switch the directory:
    Program Files\Microsoft SQL Server\mssql\binn

  2. At a command prompt, enter the following command:
    sqlservr –c –m

    If you are restoring the master database for a named instance, issue the following command instead, where name is the name of the named instance:

    sqlservr –c –m –s name

     

  3. Leave the command prompt open.

Step 4. Restore Master Database from Most Recent Backup

  1. Open Yosemite Server Backup; the service should be stopped.
  2. Create a restore job, selecting only the master database.
  3. Run the restore job.

    This may take some time, typically 10 to 15 minutes, depending on the size of the master database. Restore only the master database while in single user mode. Do not restore any other databases.

    If for some reason, your restore operation doesn’t work, rebuild the master database and attach all of your databases that reside in the data directory. To attach databases:

    • In Enterprise Manager, right-click Databases and select Attach Database.

    • In Query Analyzer, write and run a script that is similar to the following sample:

      EXEC sp_attach_db @dbname = N'test_database', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test_database.mdf', @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test_database.ldf'

     

Step 5. Apply Changes to Master Database

  1. Go to the SQL Server Manager and right-click the SQL server instance. Select Properties to open the SQL Server Properties window.
  2. Under the General tab in the SQL Server Properties window, open the Startup Parameters window and remove "–m" from the list of existing parameters.
  3. Restart the SQL server instance. (Right-click the SQL server instance and select Stop; right-click the SQL server instance and select Start.)
  4. If there have been no changes to the master database since the last dump, then proceed to Step 6. Drop Invalid Databases. 
  5. If login IDs or devices have been added to or dropped from the master database since the last backup, those changes must be reapplied. Restart the server and reapply the changes manually or from saved batch files.
  6. If databases have been created, expanded or shrunk since the last dump of master, those databases must be dropped and then restored.

Step 6. Drop Invalid Databases

Use the SQL Enterprise manager to drop any invalid database devices and databases from the newly restored master database.

If you are recovering from a disaster where you have lost a database device file, the master database you have just restored still contains a reference to it. Yosemite Server Backup will not be able to restore any databases contained on the database device until the file is restored or the database device is dropped. If the database device is dropped, Yosemite Server Backup will automatically recreate the device when a database contained on the device is restored.

Step 7. Start Yosemite Server Backup and SQL Server Services

Start the Yosemite Server Backup service by using one of the following methods.

Method 1. Windows Command Line

  1. At a command prompt, switch the directory:
    C:\Program Files\Barracuda\Yosemite Server Backup
  2. At a command prompt, enter the following command:
    ytwinsvc -s  

Method 2. Microsoft Management Console

  1. Right-click My Computer, and then click Manage.
  2. In the left pane, click Services and Applications Services. 
  3. In the right pane, right-click the Yosemite Server Backup service, and then click Start.   
  4. Restar t the SQL Server service using the SQL Server Enterprise Manager.

Step 8. Restore the msdb Database

When restoring the msdb database, keep the following considerations in mind:

  • The msdb database supports SQL Executive and provides a storage area for scheduling information. The schedules that you implement using SQL Enterprise Manager are maintained in the msdb database. This includes such things as the tasks that you schedule from the Task Scheduling window, the automatic backups you schedule from the Database Backup/Restore window and all replication tasks, which are automatically created by the system if the server is configured as a replication distributor.

  • During installation of a server, the setup program automatically creates two devices (of 2MB and 1MB) on the same disk drive as the master database and then places the msdb database on the 2MB device (MSDBDATA) and its transaction log on the 1MB device (MSDBLOG). Scheduling information is then stored in this database.

  • During a rebuild of the master database, the setup program drops and re-creates the msdb database, which results in a loss of all scheduling information.

Last updated on