Data Management

Undocumented tips for restoring your SQL 6.5 master database

Worried about what could happen if you lost your SQL 6.5 master database? Learn how to restore it properly with these undocumented tips from Mark Solomon.


If you’ve never designed and implemented a disaster-recovery strategy for your structured query language (SQL) server, it's not too late to start. I have a client performing this action for the first time because they want to port their SQL Server 6.5 box from a multi-purpose application server to a more powerful NT Server running only SQL Server 6.5. The new box will be Windows 2000-compliant and will be used to upgrade SQL 6.5 to SQL 7.0 at a future date.

Whenever you make such changes, it’s best to have a recovery plan should something go wrong. One of the first steps I perform when creating a disaster-recovery technique is testing the restore functionality of the master database.

I’ve noticed that if you can get the master to restore properly, a recovery operation runs much more smoothly. I’ve also discovered some undocumented techniques that could prove helpful when restoring the master database.

To help you understand these techniques, I’ll explain what you need to know about the utilities provided with SQL to help restore the master, as well as how SQL Server 6.5 maps data. With these techniques in mind, I’ll share the methods I’ve learned for avoiding pitfalls when restoring a master database to a new SQL Server 6.5 installation.

SQL Server 6.5 restores utilities
First, as many of you are already aware, SQL Server 6.5 provides a system-stored procedure called sp_help_revdatabase. The sp_help_revdatabase procedure prints well-documented SQL statements you can use to rebuild your SQL server databases from scratch, including the master database, in the event of failure. However, as good as this procedure is, it does not include everything you need to know for the restore. For example, sp_help_revdatabase does not include the following:
  • Device sizes
  • Device locations
  • Virtual page starting points

Consequently, you need to document your device sizes and locations. Consider documenting the output of the master database’s sysusages and sysdevices table, too. As you will see, sysusages and sysdevices contain valuable virtual starting-point locations, via the vstart column, that may be necessary to know if you need to restore a database. Create a SQL script that automatically creates your devices and sizes them properly in the appropriate locations.

Also, sp_help_revdatabase output contains flaws of which you need to be aware:
  • Explanatory statements that are not properly commented, causing errors when executing. Solution: Delete or properly comment the SQL code that sp_help_revdatabase has generated.
  • Create statements for databases that have already been created by default. Solution: Delete Create statements that attempt to create the master, model, msdb, and tempdb databases.
  • Sp_dboption statements that attempt to modify the master database and cause errors. Solution: Delete the sp_dboption statements that attempt to modify the master database options.

In short, if you want to use the sp_help_revdatabase procedure to help you recreate your databases, then you must also edit the output that it provides.

Virtual starting points and sysusages
Understanding virtual starting points may help you avoid confounding errors when you attempt to restore a database, including the master database. A virtual starting point is called vstart in the sysusages table and represents a virtual page number that SQL uses to keep track of devices.

Vstart entries represent a chronological roadmap that shows the order in which devices were created and/or altered. In fact, every time you create or alter a database, an entry is added to sysusages containing a vstart location. For example, if you were to conduct a select on sysusages, ordered by the vstart column, you would see the very order in which you created or altered each and every database within your SQL Server.

Restoration
In order to restore the master or other database successfully from a backup, it may be necessary to alter databases according to their vstart order. Often, the sp_help_revdatabase procedure provides the appropriate order to do this, but sometimes it drops the ball. In fact, if you have added more than one database to a device, the output from sp_help_revdatabase will probably be insufficient for regenerating the databases that use the device so that they can be restored from a dump.

For example, I used the output from sp_help_revdatabase to recreate our master. Sure, it altered the master database just fine. But, when I attempted to restore the master database from a disk dump, I received the following errors:
Mesg 18653 : Buffer fc38c0 from database 'master' has page number 0 in the page header and page number 14853 in the buffer header
Mesg 17218 : udread: Operating system error 38(Reached end of file.) on device 'C:\MSSQL\DATA\MASTER.DAT' (virtpage 0x00013209).

The reason why this happened is sp_help_revdatabase scripted the wrong order for altering the master device for the databases that use it. For example, sp_help_database generated the following order (excluding comments and go statements for brevity as well as Create Database statements for databases that have already been created):
CREATE Database Admin on master = 10
ALTER Database master on master = 8
ALTER Database master on master = 25
ALTER Database msdb on master = 40
ALTER Database tempdb on master = 8
ALTER Database tempdb on master = 100

However, the actual order that needs to be implemented is the following:
Altering the tempdb database for 8 MB
Creating the Admin database for 10 MB
Altering master for 8 MB
Altering tempdb for 100 MB
Altering master for 25 MB
Altering msdb for 40 MB

After altering the master device in the proper sequence, I was able to successfully restore the master device. Right now, you may be wondering how you can determine the proper order for modifying your master device on a new SQL server so you can restore your master database. Lucky for you, I created a select statement that uses the sysusages, and sysdatabases table to gather virtual order sequence from your source master database.

But, before I show you that query, you will need to gather some essential information from the sysdevices table in the source master database. The sysdevices table contains two columns called low and high for each device on your SQL server that represent the starting virtual page and the ending virtual page for that device. I collected this starting and ending information from the sysdevices on the source master database and found that the original master device started at virtual page 0 and ended on virtual page 127999.

Using this information, I ran the following query to find out the order in which that original master device was modified:
select u.size,
u.vstart,
d.name
from sysusages u,
sysdatabases d
where vstart <= 127999
and u.dbid = d.dbid
order by vstart

This produced the following output:

Size Vstart Name
1536 4 Master
512 1540 Model
512 2052 Pubs
1024 2564 Tempdb
7168 3588 Master
1024 10756 Pubs
4096 11780 Tempdb
5120 15876 Admin
4096 20996 Master
51200 25092 Tempdb
12800 76292 Master
20480 89092 Msdb

The first six entries represent how SQL Server 6.5 alters the master devices when it installs. Because SQL Server 6.5 alters the master devices identically on every install, our master rebuild also contained the first six entries. As you can see, the remaining six entries show the order that I need to alter our master device on the new server—Bingo!

The key here is to retrieve this valuable information from sysusages and sysdatabases before your SQL server goes down for the count. Do this for any device that is used by more than one database.

One major pitfall
Unfortunately, when I altered the master device in the appropriate order and attempted to restore it from a dump, I received yet more errors combined with an unsuccessful restore. Originally, I attempted to automate the restore process, so I modified the output from sp_help_revdatabase to alter the master device in the appropriate order and executed the script.

Afterward, the master device was successfully altered, but I still received errors when restoring the master database from a dump. As it turns out, the virtual pages shown in sysusages in my target server’s master database were not identical to those in my original source server.

Confounded, I started the master restore process over with a newly rebuilt master database. This time, I walked through the identical process of altering the master device in the appropriate sequence now interactively through Enterprise Manager. Then I compared the sysusages table of the source master with that of my new target master databases. Now, the sysusages tables in both master databases were identical. I then performed the master restore and, voila! The master database restore was successful. Lesson learned.

Document how you expand your master database whether through Alter statements or interactively through the Enterprise Manager. It could make the difference when resurrecting your master database on a new SQL Server.

Mark Solomon is a senior engineer who works for a Microsoft Solution Provider and CTEC in the Cincinnati area. Mark has worked with SQL since its 4.21a release and currently performs as a SQL DBA, project manager and network engineer skilled in designing, implementing, and architecting Windows networks. Mark Solomon has earned MCSE+I, MCT credentials from Microsoft.

If you'd like to share your opinion, please post a comment at the bottom of this page or send the editor an e-mail.

Editor's Picks

Free Newsletters, In your Inbox