Being Proactive Recovering Master Database

  • Currently we have a server which is on very shaky grounds. I am ensuring taking full backups daily along with transactional backups hourly. My biggest concern is the system DB's. I have scripted out the jobs, users, and linked servers. Is there anything else I should do to be proactive in case this server soils it's linens??

  • What do you mean by 'shaky ground'? What's wrong with the server?

    Are you backing up the system databases (master, model, msdb)?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes. daily backup of system db's. Ever since we cut over from Hitachi SAN to EMC, our cluster blue screened and needed to be restored from a previous build. Since then there is more blood in the event log then imaginable and server is very unstable. It's an OS problem, not SQL but I wanted to ensure I am covered. Is there anything in the master database that is not easily restored?

  • How about attending to the root problem instead of just preparing for when it fails?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Server team is dragging their feet regarding the issue. I am ensuring CYA.

  • when you say users I presume you mean logins.

    do you have user defined error messages, user stored procedures held in master? Details of user default databases, default language, server roles granted?

    Ensure you are able to quickly rebuild the server, so have the software available, make sure you know the exact release version, service account passwords and drive layout. I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource.

    If a fix does not look to be forthcoming soon any chance of setting up a DR server via logshipping or mirroring?

    ---------------------------------------------------------------------

  • George: "I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource."

    kwilt: Sorry to step in on someone else's post requesting help, but could you explain how to create flat file copies of system databases? I've not heard of this approach. Is this something that should be routinely done?

  • I'm not sure how you saved out your users, but you might want to take a look at sp_help_revlogin. It scripts them out keeping the same SID (avoiding orphaned users), and the passwords are hashed, so it's secure.

    Have you scripted out the user roles?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Yep Wayne. That is what I always use when moving users, especially local ones! Thanks!

  • kwilt (3/23/2010)


    George: "I would have flat file copies of all the system databases to speed up the recovery process, especially mssqlsytemresource."

    kwilt: Sorry to step in on someone else's post requesting help, but could you explain how to create flat file copies of system databases? I've not heard of this approach. Is this something that should be routinely done?

    simply stop SQL and copy the system database files off to another location (not tempdb). Then if the master database in particular is ever corrupted you can slide the copy back into place to get SQL up again without having to do a master rebuild. You should do this every time you patch the server, no need to do more often that that.

    For the mssqlsystemresource database this is the only way you can get a 'backup' of it.

    ---------------------------------------------------------------------

Viewing 10 posts - 1 through 9 (of 9 total)

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