none
Question about rebuild and restore 'master' database on sql server 2005

    Question

  • Hi, Gurus,

     

    We have a problem now that some of the system objects within the master database are missing. In regard of either rebuild or restore from a mostly recently backup of the master database, I am not clear if we can just simplly restore the master database from the backup and in this case we wont be able to lose our user databases which are already created? Or do we always have to rebuild the master database before we restore it but if so we will always have to restore all other use databases as well since once we rebuild our master database then all the other databases are cleared?

     

    Hope it is clear for your help and I am looking forward to hearing from you shortly for your help.

    Thanks a lot in advance.

    Best regards,

    Yours sincerely,

     

     

     

    Friday, September 26, 2008 10:01 AM

Answers

  • You're right. If you have a good backup of the master database, you can restore it and you user databases should not be affected.


    Remember that when reverting to a backup of master any system changes may need to be reapplied eg new/changed login details.


    HTH!

    Friday, September 26, 2008 11:06 AM

All replies

  • * Moved to Disaster Recovery and Availability

     

    When you say some of the system objects are missing, do you mean before or after a RESTORE?

     

    Follow the following article on how to RESTORE a backup of the master database.

     

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

     

    If you restore a good backup of your master database, your user databases should all be unaffected (assuming the data/log files are still in tact). If you rebuild the master database you will have to attach the user databases as master will not have any knowledge of them.

     

    HTH!

     

     

     

     

    Friday, September 26, 2008 10:44 AM
  • Hi,

     

    Thanks a lot for your very helpful advices.

    Well, the master database is never being rebuilt after the installation of the sql server instance. Some of the system objects are missing with time going past. I only wonder if I can restore the master database from its backup without rebuilding it so that all user databases wont be affected.

    From your suggestion above, I would assume we can simply restore a good backup of the master database without rebuilding it and at the same time keeping all of the use databases unaffected as well?

    Thanks a lot.

     

    Best regards,

    Yours sincerely,

     

     

     

     

    Friday, September 26, 2008 10:57 AM
  • You're right. If you have a good backup of the master database, you can restore it and you user databases should not be affected.


    Remember that when reverting to a backup of master any system changes may need to be reapplied eg new/changed login details.


    HTH!

    Friday, September 26, 2008 11:06 AM
  • Hi,

     

    Thanks a lot. Now I have a clear plan of backing up the Master database on a daily basis and thus just need to simply restore it from its backup here without affecting all other user databases in case of any corruption or missing of system objects of it.

    Thanks a lot again for your very advanced advices. It's absolutely great help.

     

    Best regards,

    Yours sincerely,

     

     

     

    Friday, September 26, 2008 11:19 AM