Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS65,SS7
Expertise: Intermediate
May 5, 2001

Restore the SQL Server master database

Restoring a corrupted master database is one of the most difficult tasks you may have to deal with. If this catastrophic event happens, here's how to proceed.

First, you need to rebuild the master database. Details on this operation depend on the SQL Server version you run. With SQL Server 6.5 the only way is to re-install the program, whereas SQL Server 7.0 provides an utility called Rebuildm.exe, in the \Mssql7\Binn directory.

Set SQL Server on single-user-mode, by starting it with the -m switch. Next, restore the master database from the latest avaible backup. At the end of the restore procedure, after verifying that all databases are working correctly, you can proceed restoring the MSDB database. When this step completes, stop SQL Server and start it again in normal mode. After the restart remember to check that all the user databases are working properly. If they aren't, the action to undertake depends on the SQL Server version.

With SQL Server 6.5, perform a DISK REINIT, which restores the data about the system tables usage in case of an existent device but missing data in the sysusages table.

DISK REINIT
NAME = 'logical name'
PHYSNAME = 'physical name'
VDEVNO = virtual device number
SIZE = number of 2K blocks
[, VSTART = 'virtual address']
SQL Server 7.0 doesn't support DISK REINIT any longer, but the same operation is available through the sp_attach_db system stored procedure, which connect a database to a server:
sp_attach_db [@dbname =] 'database name', [@Filename1 =] 'File 1 name' [, ... 16].
If this sequence of operations can't solve the problem, you'll need to rebuild each device and database and then perform the individual restores. The importance of regular backups, and in particular the master database master, can't be overemphasized enough, as it can assure the server stability and a quick restart in case of problems.

########################################################

This tip has been originally published on Microsoft Italia's web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################

Giuseppe Dimauro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date