Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    99

    Unanswered: trouble restoring Master DB

    I am running through a Disaster Recovery scenerio and I am having trouble restoring the Master and msdb DBs to a recovery box.

    My production is a default Instance on WIN2k Server - SQL Server is 2000 Standard, sp3a

    The box I'm trying to recover to is a Named Instance on WIN2003 server - SQL Server 2000, sp3a

    I stopped/restarted my recovery server with -m and -T3608 startup parameters but when I attempt to restore the master backup from my production box to the recovery box, I get an error - it's a blank dialog box with a big red X and it says: Microsoft SQL-DMO - ODBS SQL State - HY000

    when I check the sql server log: it shows the Server started in single user then it says recovering master db then starting up master DB then it just hangs..

    Server event viewer shows this: 18052:error 3151, severity 21, state 0

    not much to go on.. does anyone know what I'm doing wrong???

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you trying to restore master? You aren't storing application specific code or data in master, are you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    99
    definitely no application specific anything in Master. I thought i'd have to restore master and msdb to have a fully functioning DR server - but what I'm testing now is a script to restore logins (with same password and SID) and then another script to restore user DBs from most recent backup.. that should do the trick for DR - I hope

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should just have to restore user dbs for recovery. The only thing that can get tricky is database logins and object ownership.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    From SQL BOL:

    How to rebuild the master database (Rebuild Master utility)
    To rebuild the master database

    Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

    In the Rebuild Master dialog box, click Browse.

    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.

    Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
    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.

    In the Rebuild Master dialog box, click Rebuild to start the process.
    The Rebuild Master utility reinstalls the master database.

    Note To continue, you may need to stop a server that is running.


    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Mar 2004
    Posts
    99
    Thanks Blindman. What I ended up doing was recovering my logins from a script then restoring User DBs and running

    EXEC sp_change_users_login 'Auto_fix','USERID'
    EXEC sp_defaultdb 'USERID', 'DBName'

    where needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •