A damaged master database can cause the SQL Server to fail to start, and other error
conditions. You must restore the master database if it is damaged.
Before you begin
- Set single-user mode for restore operations.
- Always keep an up-to-date backup of your master database because the master
database contains the system catalog. The system catalog contains important
information about the SQL Server configuration.
- Ensure that you back up the master database after any changes that update system
tables. For example, back up the master database after you use any of these statements:
- ALTER DATABASE
- CREATE DATABASE
- DISK INIT
- DISK RESIZE
- DISK MIRROR
- DISK UNMIRROR
- DISK REMIRROR
- Various DBCC options such as SHRINKDB
- System stored procedure such as: sp_dropremotelogin, sp_addumpdevice,
sp_dropdevice, sp_addlogin, sp_droplogin, sp_addserver, sp_dropserver,
sp_addremotelogin
About this task
Restriction: The Microsoft Management Console (MMC) cannot connect to a SQL Server instance
that is started in single-user mode. When the SQL Server instance is in single-user
mode, you must use the command-line interface, tdpsqlc.exe, to
restore the master database.
Procedure
- Click Start > All Programs >
Tivoli Storage Manager > Data Protection
for Microsoft SQL Server > SQL Client - Command
Line.
- Start the SQL Server in single-user mode. For more information about SQL Server startup options, see the Microsoft SQL Server documentation.
- Use Data Protection for SQL Server to restore the master database. When the master database finishes the restoration process, the SQL Server
shuts down and an error message is displayed. The message indicates that the
connection to the SQL Server is lost. This loss of connection is
expected.
- Restart the Database Engine to restore SQL Server to the typical multi-user
mode.
- Run the SQL Server setup program to rebuild the master database. When you rebuild the master database, use the same character set and sort
order as the master database backup that is to be restored.
- Manually reapply any changes to the master database that occurred after the
date of the database backup that is used to complete the restore
operation.
- Restore the msdb database. During the process of rebuilding the master database, the SQL Server setup
program drops, and then re-creates, the msdb database.
Therefore, you must restore the msdb database with the master
database.
Results
After the master database is restored, you can use the MMC to back up and restore
individual databases that are operating in single-user mode.