Post reply

restoring the MASTER

  • November 11, 2007 at 6:44 am

    #376965

    hi all

    am testing a disaster & recovery plane which include the case in which i need to recover the master database (2005)

    first am talking the database into single-user mode "this step is going all right"

    second am trying to restore the master when using the SQL Server Management Studio i get an error message during the recovery process and when trying to recover the database using sqlcmd i get anther error "only one administrator can access the server the database is in single-user mode" and i cant find this ""second administrator"???

    " dont know what to do if u please help...

    ..>>..

    MobashA

  • November 11, 2007 at 9:10 am

    #750102

    You need to use SQLCMD. SSMS and Enterprise Manager open multiple connections to the server.

  • November 11, 2007 at 9:26 am

    #750104

    i did used sqlcmd and i get the same error

    ..>>..

    MobashA

  • November 12, 2007 at 7:46 am

    #750250

    Make sure that you stop SQL Server Agent before you initiate the single-user mode and then restore the master from backup using SQLCMD. I've run into the same error when I have neglected to stop the SQL Server Agent.

    Here's a good article, guide for restoring the master db. http://msdn2.microsoft.com/en-us/library/ms175535.aspx

    Good Luck!

  • November 12, 2007 at 7:57 am

    #750257

    Also make sure you close out of SSMS while connecting with SQLCMD.

  • November 13, 2007 at 3:08 am

    #750533

    yes Of course man??

    ..>>..

    MobashA

  • November 13, 2007 at 5:28 am

    #750598

    Refer the below link, i think this may help you.

    How to Rebuild System Databases in SQL 2005

    Regards..Vidhya Sagar
    SQL-Articles

  • November 13, 2007 at 5:37 am

    #750607

    thats good ,

    am not trying to be nagy but i want to restore the master database not rebuild it,the tow operations are much differente as i know .

    check this out i have the following:

    srv-1 srv-2

    master-1 master-2

    and i want to resotre the master-2 on the srv-1 and replace it with master-1

    this is exactly what i need to do.

    ..>>..

    MobashA

  • November 13, 2007 at 9:46 pm

    #750922

    Hi,

    Check http://msdn2.microsoft.com/en-us/library/ms190679.aspx

    For SQL 2000

    1- Add the backup device

    2- Stop SQL Server

    3- Bring SQL Server to single-user mode via the command line For example : c:\program files\microsoft sql server\mssql.1\mssql\binn> sqlservr.exe -c -m

    4- logon into SQL Server as sa/trusted connection

    5- restore the master

    Regards,

    Ahmed

  • November 13, 2007 at 11:49 pm

    #750964

    thanks a lot for helping me ,am doing exactly as u said but on step

    4- logon into SQL Server as sa/trusted connection

    i get an error message that only one DBA can connect to sql server at a time, i closed every connection and every service was useing the sql server but stil the same error??????//

    ..>>..

    MobashA

  • November 14, 2007 at 2:31 am

    #750995

    Try the trace flag 3607 which should skip the recovery of the user databases. Rememeber this trace flag I have used in SQL Server 2000 just try and see if it works. Please update us if it worked .

    Start sqlservr.exe -c -m -T3607

    may help you http://msdn2.microsoft.com/en-us/library/ms188236.aspx

    All the Best

    Minaz

    "More Green More Oxygen !! Plant a tree today"

  • November 14, 2007 at 3:11 am

    #751006

    i did try trace flag 3607 but i still couldnt connect to the sql server the same error.

    any way i had tried this please tell if will be any side effects:

    i have

    srv-1 srv-2

    master.mdf-1 master.mdf-2

    master.log-1 master.log-2

    what i did is shutdown the srv-1 services then copy the master.mdf-1 and master.log-1

    then shutdown the srv-2 services and replaced the master.mdf-2 and master.log-2 with master.mdf-1 and master.log-1

    then start srv-2

    and every thing went good?????????????????????????///

    ..>>..

    MobashA

  • November 14, 2007 at 3:22 am

    #751008

    Hi,

    The master database contains system information and high-level information about all databases on an SQL Server (also logins !).

    You will lost all logins and databases that are not created under master-1.

    (http://vyaskn.tripod.com/sql_server_administration_best_practices.htm)

    Regards,

    Ahmed

  • November 14, 2007 at 3:25 am

    #751009

    actually i need all configurations from master-1

    and i dont care what happen to master-1.log and mdf

    ..>>..

    MobashA

  • November 14, 2007 at 3:35 am

    #751012

    If all the users are able to login ?

    Databases are working properly ?

    I doubt because the server name is different .

    If not working try changing the server name by sp_dropserver and sp_addserver.

    Still I recommended try this out in test server.

    "More Green More Oxygen !! Plant a tree today"

Viewing 15 posts - 1 through 15 (of 16 total)

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