Post reply

Restoring System Databases

  • February 20, 2006 at 10:37 am

    #167633

    I am testing my (new) Disaster Recovery Plan. The plan calls for a complete rebuild of a new server with a fresh instance of SQL Server 2000 SP4. This is in case our building burns down and all our servers are destroyed and I need to build a new server from our tape backup that is kept off site.What is the steps for restoring the system databases from backup? Do I restore all other databases first and then restore master, msdb, model? Or do I restore the system databases last?

    I figured that all the user databases are currently in master so they should be restored first otherwise there might be an error when starting the service. Am I completely off base?

    I did google for the answer, but all information was restoring master on the same instance, not on a different server.

    Thanks,

    Christi

     

  • February 20, 2006 at 12:15 pm

    #622098

    I'v done this same process for our DR plan and here are the steps I use:

    1.  Install SQL Server and all service packs on the new server, (If the file locations are the same as the old server then things are much easier).

    2.  From the Command prompt stop the services by running NET STOP MSSQLServer.

    3.  From the Command prompt navigate to the directory where the SQL Server executable and start the server in single user mode by executing sqlservr -m.

    4.  Open query analyzer and restore master using the following script:

    RESTORE DATABASE master FROM disk = 'local path of the master backup file'

    5.  Restore msdb and model, (please note that the Agent must be stoped to restore msdb).

    6.  Restore each of the user databases.

    Things to note: 

    1.  If the tempdb files are located in a different location than there are according to the original master db then after restoring you will need to update master to the new locations or the server will not start.

    2.  Once master is restored the sa password will be reset to that of the original master db and not what it was set to durring the install of the new server.

    If you need more details let me know and I can fill in some of the blanks I skipped over.  This can be a tough thing to do and the best advice I can give you practice practice practice.  You are bound to run into little hiccups and the best wasy around them is to fix as you run into them and update your DR as you go.

  • February 20, 2006 at 3:08 pm

    #622126

    Thanks for your help, I am having a problem with master database.

    these are the steps I take

    go to command line and type in NET STOP MSSQLServer

    (that runs fine)

    C:\Program Files\Microsoft SQL Server\MSSQL\Binn> sqlservr.exe -c -m

    it runs through and this is what it gives me

    2006-02-20 13:07:09.88 server    Copyright (C) 1988-2002 Microsoft Corporation.

    2006-02-20 13:07:09.88 server    All rights reserved.

    2006-02-20 13:07:09.89 server    Server Process ID is 1316.

    2006-02-20 13:07:09.89 server    Logging SQL Server messages in file 'C:\Program

     Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.

    2006-02-20 13:07:09.89 server    SQL Server is starting at priority class 'norma

    l'(1 CPU detected).

    2006-02-20 13:07:09.94 server    SQL Server configured for thread mode processin

    g.

    2006-02-20 13:07:09.96 server    Using dynamic lock allocation. [2500] Lock Bloc

    ks, [5000] Lock Owner Blocks.

    2006-02-20 13:07:09.99 server    Attempting to initialize Distributed Transactio

    n Coordinator.

    2006-02-20 13:07:11.05 spid3     Warning ******************

    2006-02-20 13:07:11.05 spid3     SQL Server started in single user mode. Updates

     allowed to system catalogs.

    2006-02-20 13:07:11.05 spid3     Starting up database 'master'.

    2006-02-20 13:07:11.19 server    Using 'SSNETLIB.DLL' version '8.0.2039'.

    2006-02-20 13:07:11.21 server    SQL server listening on 10.1.100.219: 1433.

    2006-02-20 13:07:11.21 server    SQL server listening on 127.0.0.1: 1433.

    2006-02-20 13:07:11.22 spid5     Starting up database 'model'.

    2006-02-20 13:07:11.22 spid3     Server name is 'SQLTESTING'.

    2006-02-20 13:07:11.24 spid7     Starting up database 'msdb'.

    2006-02-20 13:07:11.24 spid8     Starting up database 'pubs'.

     

    I then log into Query Analyzer as SA

    RESTORE DATABASE master FROM disk = 'c:\master.bak'

    I then recieve this message:

    Server: Msg 9001, Level 21, State 1, Line 1

     

    Connection Broken

     

    any ideas? I have done this about 20 times in the last hour and I am about ready to pull my hair out!

    I should also note that I am doing this on a VMWare server.

     

  • February 21, 2006 at 4:09 am

    #622208

    Hi,

    Difficult proces to restore the system db's.

    I you're using Windows 2003 then you can always get a copy of the MDF and LDF system database files (using VSS/shadow copies). These files you can use to restore on your failover system by installing MS SQL + SP4. Stopping the server and just copying the files over the existing files. Just to be sure: always setup the system exactly as you would normally do. (same locations of all the files eg D:\MSSQL\DATA\MASTER.MDF and E:\MSSQL\mastlog.ldf etc).

    Regards

    JP de Jong

  • February 21, 2006 at 9:22 am

    #622343

    Thanks JP, but the Disaster Recover plan I am working on requires that there are no .mdf or .ldf files to work with. This is in case our building burned down and I had to rebuild the server from our tape backup. (that is kept off site)

    I have had very much difficulty doing this, the only good news is I have a vb script, using dmo, that scripts out all of our servers/databases so user logins etc are scripted out, in case master (or any other database) fails to restore. (this is backed up on the tape)

     

  • February 21, 2006 at 10:59 am

    #622389

    Here's everything you need to know for SQL 2000:

    http://support.microsoft.com/default.aspx?kbid=314546#top

    http://support.microsoft.com/default.aspx?kbid=246133

    http://support.microsoft.com/default.aspx?scid=kb;en-us;253817

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867

    http://support.microsoft.com/kb/224071/en-us

    http://support.microsoft.com/kb/304692/EN-US/

    Have fun !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 21, 2006 at 12:35 pm

    #622418

    Thanks Rudy, I went over all of those already.

    I think the problem is with the VMWare.

     

  • February 21, 2006 at 1:53 pm

    #622447

    I just reread the entire thread. The 'error' you are getting after the master database has been restored is normal. Anytime the master database has been restored SQL Server immediately shuts itself down. Below is an example of the last DR master database load I performed (for documentation purposes):

     The master database has been successfully restored. Shutting down SQL Server.

     SQL Server is terminating this process.

     [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData (CheckforData()).

     Server: Msg 11, Level 16, State 1, Line 0

     General network error. Check your network documentation.

     

     Connection Broken

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 21, 2006 at 2:01 pm

    #622449

    Well, if it was restored to normal, how come no logins etc were carried over? And your error message was different than mine.

  • February 21, 2006 at 2:25 pm

    #622455

    I can spot only a couple of differences between your process and mine. I use the Service Control Manager to start/stop things as a service during master database recovery. The other difference is that I do not use the "-c" option, just "-m" only. Other that that, have you tried a different backup ? If you have then I am out of ideas.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 21, 2006 at 2:57 pm

    #622466

     

    thank you for your input Rudy

    sorry, in my previous message it said my logins etc 'were' carried over, they were not.

  • February 21, 2006 at 8:20 pm

    #622498

    This is what we would do in this situation - this is from some internal doco - with a few changes for the outside world.

    It also assumes that you have recovery scripts for your user database creation - hope it at least explains the process we follow.

    When the SQL Server 2000 & service pack installation process is complete, the SQL server will be in a shut down state. Start the SQL server and SQL server agent services and change the startup setting for the SQL server agent service from manual to automatic.

     

    Perform a backup of the generic master, model and msdb databases in case they have to be restored in the event that a recovery problem arises and certain steps need to be repeated.

     

    Using your recovery script create the user databases on the new SQL server environment. This is required prior to the restore of the master database. When the master database is restored and the server is restarted, master will expect to find the devices for the user databases. If they are not present in the correct directory, errors will be generated in the errorlog about devices not being found and databases not being recovered.

     

    Shut down the SQL server and SQL server agent services and restart the SQL server in single user mode in preparation for a master database restore using the following example commands on a DOS window:

     

    Default instance:

     

    cd  c:\”Program Files”\”Microsoft SQL Server”\mssql\binn

    sqlservr.exe –c –m

     

    Named instance:

     

    cd  c:\”Program Files”\”Microsoft SQL Server”\MSSQL$INSTANCENAME\binn

    sqlservr.exe –c –m –s INSTANCENAME

     

    After the server has been started, the cursor will be sitting on the left bottom corner of the window. Leave this as is and iconize the DOS window.

     

    Restore the master database using the latest master database dump file.

     

    The restore of the master database only takes a few seconds and when complete, the following message will be generated:

     

    The master database has been successfully restored. Shutting down the SQL server.

    SQL server is terminating this process.

     

    Acknowledge this message by clicking on the OK button

     

    Once acknowledged, a few seconds later, the following message will be generated:

     

    The connection to SQL Server XYZ has been broken. The connection to the SQL Server is broken. Do you want to try reconnecting it?

     

                Reply No by clicking on the ‘No’ option and cancel out of the restore window.

     

    The server having being shut down, the DOS window cursor would have returned to it normal position on a new line.

     

    Restart the SQL server service leaving the SQL server agent service in a shutdown state. Check the errorlog to determine if everything is in order. There should now be references to the user databases.

     

    Restore the msdb database using the latest database dump file. This will recover any Maintenance Plan jobs previously set up. The msdb database cannot be restored if the SQL server agent service is running.

     

    On completion of the restore process, restart the SQL server agent service.

     

    Check to ensure that any Maintenance Plan(s) previously set up is/are now present.

     

    Restore the user databases using the latest database dump files. Cycle the server to ensure everything starts up OK. Randomly check a few user databases to ensure various user objects are present before handing the system over to users for testing.

    MAO

Viewing 12 posts - 1 through 11 (of 11 total)

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