Master database recovery problems after upgrade to 6.5

Master database recovery problems after upgrade to 6.5

Post by Tom Griffi » Sat, 15 Nov 1997 04:00:00



I performed an upgrade on a SQL Server machine from version 4.21a to
version 6.5 with no indication of errors.  As part of our upgrade process,
we test the recovery of the master database.  The recovery failed because
the structure of the master.dat device from 4.21a to 6.5 has changed.  The
first problem encountered was that the upgrade created a master database of
22mb and the Rebuild Master Database option of the SQL Server 6.5 setup
program creates a master database of 16mb.  If you try to restore the
master from a backup taken from the upgraded master into the rebuilt master
you get the message:

        Msg 3105, Level 16, Severity 0
        Data on dump will not fit into current database.  Need 22 Mbyte database.

If you expand the master at this point, you may have problems bringing up
SQL Server because the layout the master.dat device of the rebuilt master
does not match the layout of the upgraded master.dat device.  The load will
complete without error, but SQL Server will probably fail to come up.
Microsoft's soluiton to the problem is to make an OS copy of the master.dat
device immediately after the upgrade and use that as a template for the
master database recovery.  Their recommended scenario is:
        1.  Upgrade from 4.21 to 6.5
        2.  Make an OS copy of master.dat and keep it in a "safe" place (disk and
tape)
        3.  Continue to run SQL Server
        4.  If it is necessary to recover the master database:
                a.  Run SQL Setup and rebuild the master database
                b.  OS copy the master.dat saved in step 2 above over the one created by
step 4a
                c.  Restore the master database from the most current backup

The biggest problem that we have with this is that we need to keep a copy
of this "template" in synch with the master.dat device forever.  According
to Microsoft Tech Support, there is no other solution.  We asked them about
doing some kind of copy of tables out of the master and then loading that
into the rebuilt master, but they said that was not recommended or
supported.  What we are trying to accomplish, is to get to a point with our
master database that allows us to just follow the procedures for recovering
the master database that are outlined in all their documentation.

My questions are these:

1.      Has anyone else run accross this problem in their upgrade from SQL
Server 4.21 to SQL Server 6.5?
2.      If you have, what did you do to solve it?
3.      If you haven't, or have never bothered to look, are you sure that you
will be able to recover your master database after using SQL Server setup
Rebuild Master Database?
        If you go into Enterprise Manager, right-click on the master database,
choose Edit... and see a Data Size greater than 16 mb, you can be assured
that you will not be able to recover your master database after the rebuild
from any backups taken using SQL Server!!!  You will get the 3105 message
above when you try to load the database from the backup; or you will be
able to load the master from the backup, but it is highly likely that SQL
Server will fail to start because of the master.dat device's change in
segmentation.  

 
 
 

1. Recovery if master corrupt? (SQLserver 6.5)

Why is to-the-minute recovery not possible if master is corrupted together
with application database?
Testing a backup strategy I try the following:
1. Database dump, master dump, msdb dump
2. Insert values in appl database
3. Log dump
4. Insert values
5. Stop server
6. Crash application database and master database
7. Rebuild master database
8. Restore msdb
9. Log dump with no_truncate
10. Recreate database
11. Restore database, restore normal logdump
12. Trying ty restore last logdump(no_truncate) gets out_of_sequence

If I keep master intact and only crash application database, my test
sequence is OK for the application part and the last logdump can be applied.
Is some part of the log sequence info kept in master or msdb that prevents
the last log from getting correct sequence info? Is it possible to force
acceptance of the last log part?

2. Help! Data Control Refresh?

3. Recovery of SQL 6.5 Master DB

4. Testing if a table exists

5. SQL 6.5 database recovery problem -db is suspect

6. Sybase 'connections' = 'users'?

7. master db corrupt after 6.5 to 7.0 upgrade

8. Opening my database with a browser.

9. Restore 6.5 master database 6.5

10. Need help - Master database recovery

11. Master database recovery

12. Sybase ASE 11.0.3. master recovery problem

13. Database recovery when master can't be read