Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Red face Unanswered: Is the re-start done?

    Hi,

    I was trying to restore a master database. This is what I did:
    1. rebuild the master database and it was successful
    2. restart server in single mode with
    sqlservr.exe -c -m
    and I got the following window:

    ************************************************** ********
    2006-01-27 10:30:01.49 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X
    86)
    May 3 2005 23:18:38
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    2006-01-27 10:30:01.52 server Copyright (C) 1988-2002 Microsoft Corporation.

    2006-01-27 10:30:01.52 server All rights reserved.
    2006-01-27 10:30:01.52 server Server Process ID is 600.
    2006-01-27 10:30:01.52 server Logging SQL Server messages in file 'e:\Microso
    ft SQL Server\MSSQL\log\ERRORLOG'.
    2006-01-27 10:30:01.54 server SQL Server is starting at priority class 'norma
    l'(4 CPUs detected).
    2006-01-27 10:30:01.71 server SQL Server configured for thread mode processin
    g.
    2006-01-27 10:30:01.74 server Using dynamic lock allocation. [2500] Lock Bloc
    ks, [5000] Lock Owner Blocks.
    2006-01-27 10:30:01.99 server Attempting to initialize Distributed Transactio
    n Coordinator.
    2006-01-27 10:30:04.04 spid3 Warning ******************
    2006-01-27 10:30:04.04 spid3 SQL Server started in single user mode. Updates
    allowed to system catalogs.
    2006-01-27 10:30:04.04 spid3 Starting up database 'master'.
    2006-01-27 10:30:04.29 server Using 'SSNETLIB.DLL' version '8.0.2039'.
    2006-01-27 10:30:04.29 spid5 Starting up database 'model'.
    2006-01-27 10:30:04.29 spid3 Server name is 'SQLTEST1'.
    2006-01-27 10:30:04.31 spid7 Starting up database 'msdb'.
    2006-01-27 10:30:04.31 spid8 Starting up database 'pubs'.
    2006-01-27 10:30:04.31 spid9 Starting up database 'Northwind'.
    2006-01-27 10:30:04.31 server SQL server listening on 10.10.4.17: 1433.
    2006-01-27 10:30:04.33 server SQL server listening on 127.0.0.1: 1433.
    2006-01-27 10:30:04.34 server SQL server listening on TCP, Shared Memory, Nam
    ed Pipes.
    2006-01-27 10:30:04.36 server SQL Server is ready for client connections
    2006-01-27 10:30:04.67 spid5 Clearing tempdb database.
    2006-01-27 10:30:05.54 spid5 Starting up database 'tempdb'.
    2006-01-27 10:30:05.70 spid3 Recovery complete.
    2006-01-27 10:30:05.70 spid3 SQL global counter collection task is created.

    ************************************************** **

    Now an hour passed and the window is still there. The SSQL service manager on the toolbars still has a red icon but not green. My questions are how do I know the process is done? What is the next step after SQL global counter collection task? Where can I find more information?

    Thanks a lot.

    Mark

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    My recollection is from a desperate incident that occurred to me more than seven years ago when I had to restart a failed SQL Server instance. As I recall, when you run it from the command window like you've done, it stays in the state "forever". It is in essence, the SQL instance running (just on the console instead of as a service).

    It's been a loooong time since I recovered a master database (note to self, something to practice soon). What I recall doing from before is getting it all started and then shutting the service down and then restarting again in "normal" mode.

    Like I said, it's been a while.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Once you see "Recovery Complete", you should be able to open a Query Analyzer or OSQL session with the server, in order to restore the master database. You are essentially looking at the errorlog in the command window, so the next entry will be the next error, warning, or information message that SQL Server sees fit to put in the errorlog.

    Not sure what other information you would be after in this case. You can see if sqlservr is running in task manager, but you already know it is running at the command line.

  4. #4
    Join Date
    Dec 2005
    Posts
    28
    Thanks all. I understand now.

  5. #5
    Join Date
    Dec 2005
    Posts
    28
    I managed to restore the master database on a different server but I cannot modify the jobs I created before on the new server because the server name gets changed.

    Should I change the server name in the master and msdb databases? Will sp_dropserver old_server_name and sp_addserver new_server_name work?

    Thanks a lot.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by mark1212
    I managed to restore the master database on a different server but I cannot modify the jobs I created before on the new server because the server name gets changed.

    Should I change the server name in the master and msdb databases? Will sp_dropserver old_server_name and sp_addserver new_server_name work?

    Thanks a lot.
    I had a similar problem once before. Be sure to run sp_dropserver and sp_addserver. However, I think I ultimately had to go into the msdb database and open the sysjobs table and manually edit the "originating server" field to the new server name.

    If I recall correctly, that did the trick.

    Regards,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Dec 2005
    Posts
    28
    I ended up using update sysjobs table like this:
    update sysjobs set originating_server = new_server
    and the jobs are working now.

    I also notice that several other system tables have the old server name, should I change them as well?

    Thanks.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Not if they cause no problems. This is what is can be referred to as "poking the bear" in the SQL Server world.

    The master database is never too happy about being restored to a server of a different name. It can be done, but it should be avoided for these sorts of reasons. Is this for a hardware upgrade?

  9. #9
    Join Date
    Dec 2005
    Posts
    28
    No, I am practising the database disaster recovery. The master database recovery is one Scenario.

Posting Permissions

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