Post reply

Restore Master database failed!

  • May 17, 2005 at 9:40 pm

    #109405

    Hi to all!

    I tried to restore my master database to other machine, My first machine is win2K advance sever, database mssql 2000 standard edition and the other one is win2k advance server, database mssql 2000 Enterprise Edition. I also performed this: (please see the following below)

    Machine 1 (standard edition)

    1. backup master database.

    2. File transfer to machine 2

    Machine 2 (enterprise edition)

    1. stop sql service

    2. executed sqlservr.exe -c -m

    3. open the sql enterprise manager.

    4. restore the master database. (master .mdf and ldf are all correct)

    after I performed all the things above. I got a message during restoring.Please see the error message below.

    Microsoft SQL.DMO(ODBC SQL State:42000)

    The backup of system database on device <backup file name> cannot be restored bacause it was created by different version of the server(134218488) than this server (134217422). Restore database is terminating abnormally.

    is there something wrong in restoring database in different sql version?

    Please advice.

    Any inputs and/or advice are highly appriciated.

    Best Regards,

    Arnold

  • May 18, 2005 at 1:25 am

    #559487

    see this article for explanation/solution to your problem:

     

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

  • May 18, 2005 at 9:54 pm

    #559768

    Hi again,

    I tried to reinstall the sql server 2000 in machine1. The following are the result of @@version

    Machine1 - Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    Machine2 - Standard Edition on Windows NT 5.0   (Build 2195: Service Pack 4)

    After I reinstall the got the same error message. The build version are the same except for the edition.

    Thanks in advance.

    Please help.

    Regards,

    Arnold

  • May 19, 2005 at 2:01 am

    #559783

    2195 is OS build

    @@version output starts like this:

    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

    where 760 denotes SP3 in my case

    or you can use

    select serverproperty('productlevel')

  • May 19, 2005 at 9:26 am

    #559877

    Hi !

    Why do You want to restore the Master DB ?

    Best Regards,

    Claus Munch

    Denmark

  • May 19, 2005 at 9:50 am

    #559897

    Just a thought. You want to reconsider why you want to restore your master db to begin with.  Restoring all your user databases and copying over all logins, then resyncing users and logins may be a better sequence. 

    Francis

  • May 19, 2005 at 7:08 pm

    #560082

    Hi to all,

    Thanks for giving me your time....

    restore is part of a monthly activity here the company that i been wooking for.. this is just a test restore and part of disaster recovery plan (DRP). I need to test all the backup including the system database. And the problem like this, i don't think i can minimized the downtime of my production db case of any disaster.

    Pls. help me........

    Thanks in advance.

    Regards,

    Arnold

  • May 19, 2005 at 7:50 pm

    #560088

    Hi

    I checked the the productlevel using select serverproperty, the following are the result.

    machine1 - sp3 (prod)

    machine2 - rtm (test machine)

    I think i need to download sp3 to machine2 , I also checked the microsoft web site to download sp3, but unfortunately i only found sp3a , do you think this could help me to solve my burden restore.

    Thanks in advance.

    Regards,

    Arnold

  • May 20, 2005 at 1:37 am

    #560112

    sp3 and sp3a are equivalent in this case. Apply sp3a to test machine and restore will succeed. Be aware of some other things to consider. if you have a different disk/directory structure on your test machine, you will immediately after restoring master run into trouble, because paths to other system dbs won't match and your server won't go up. Also you will need to change server name in system catalog and originating server for the jobs.

    If you want to reach minimal downtime for your production db in case of disaster, you should turn to log shipping, replication or clustering.

  • May 20, 2005 at 3:45 am

    #560128

    Well, heres the procedure we use at the company i work for.

    Rebuild Master/Fresh install of SQL Server

    Restore other system databases.

    Restore User databases

    Relink users and logins

    -Claus

  • May 20, 2005 at 12:20 pm

    #560322

    Arnold,

    I've never tried it, so I can't say that it definitely won't work, but... It is my sincere opinion that you cannot restore a master database from a Standard Edition backup to an Enterprise Edition instance of SQL Server.  The master database isn't just a user database, it is an integral part of SQL Server.  The two instances must not only be the same service pack level, they must be the same edition.  You could restore user databases between the two, but not system databases.  Now, what you could do, is restore the master database AS A USER DATABASE to your Enterprise edition server, and then use the data in the database as a reference to update the server as fhanlon suggested. 

    RESTORE OldMaster

      FROM DISK = 'C:\backups\master.bkp'

      WITH MOVE master to 'c:\mastercopy\mastercopy.mdf',

      MOVE mastlog to 'c:\mastercopy\mastercopylog.ldf'

    Don't attempt to overwrite the existing master database files!

    If you MUST restore master, Install the same edition and service packs that existed on the previous server.  By the way, service pack 3 and service pack 3a should be compatible.

    Steve

  • May 22, 2005 at 11:44 pm

    #560426

    Hi!

    Thanks for the inputs, suggestion, tips and other helpful things.

    I really appreciated all those things.. Anyway I successfully restored my master db, sysdb and user db. I got my SP3 in my officemate.

    I hope some day, I can help and share my thought with all of you.

    Thank you very much. 

    Regards,

    Arnold

     

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

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