restore master database

  • I have the SQL server 2000 production databases on windows NT, my boss ask me to upgrade Win2k with SQL 2000 with formatting the drive, after I did all those, start SQL server in the single user mode, restore the master DB, the message showed that ‘the database restored successfully, the server will be shut down’, then I click OK, another pop-up message ‘The connection to sql server ' ' has been broken’ after few second.

    I did not know how to solve the problem, I rebuilt the master DB, used sp_attach_db to restore the user databases, I knew there would be the login issues, then I used sp_change_users_login to update the logins one by one. Could anyone tell me what should be done before/during/after restore master database? Thanks.

  • But you can connect to SQL or can't... If so wich what user are you connecting?

    Did you change the login authentication type?, or specify any other windows login account

    for any of your services? Or even change the protocol used in the client network utility)

  • Once you restore the master, you should be able to connect normally and restore your other files (or attach, depending on which technique you prefer). If you don't have model and msdb where sql expects, you'll get errors - I think the trace flag is T3607 to override recovery.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Are you able to start SQL Server and Agent services after master database restoration completion? Try to login SQL Server with EM or ISQL to verfiy following.

    1. Your SQL Server version and service pack level.

    2. Make sure 'SA' password is not blank.

    3. Adjuts SQL Server memory configuration according to machine available resources.

    4. Attach all user databases.

    5. Check SQL Server Errorlog for any errors.

    6. Restore or attach MSDB back or recreate all jobs / DTS packages.

    7. Setup replication/log shipping/full test search/SQL Mail/Linked Server if you had them before.

    8. Ensure your application is able to access databases.

  • sql server could not be started after restore master, the message showed successful restore, few second later, 'sql server connection broken' message popup, I tried serveral times on SQL2000 SP2 and SP3, did not work, I have to attach user DB without restoring master, then solve the ophan logins.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply