Serious issue on 2008R2 - lost all system databases

  • Mortimer452

    Old Hand

    Points: 313

    Short story - someone messed up on the SAN and deleted the drive that contained my master, model, and msdb databases. So, I have a MSSQL instance with a crapload of DB's, but no system DB's. SQL 2008R2 (SP1 I think)

    I do have solid backups of the system DB's, as well as all other DB's that were on that drive.

    Reading up on how to rebuild these - just looking for any advice from people who may have had to do this before.

  • FridayNightGiant

    SSCertifiable

    Points: 7091

    Do you have recent valid backups of these databases?

  • Mortimer452

    Old Hand

    Points: 313

    FridayNightGiant (6/2/2016)


    Do you have recent valid backups of these databases?

    Yes, I should have mentioned, I do have solid backups of all the system DB's, and other DB's that were on that drive.

  • FridayNightGiant

    SSCertifiable

    Points: 7091

    Check out the link here - https://msdn.microsoft.com/en-ca/library/dd207003(v=sql.105).aspx

    You need to run the setup command with an action of REBUILDDATABASE

    Once complete I would stop the SQL service and start it in single user mode and restore the master model and msdb databases.

  • Mortimer452

    Old Hand

    Points: 313

    Everything is back online now.

    For reference, the MSDN articles below explained pretty much everything you need to do. Worked awesome. Just another reminder of how crucial it is to have good backups of all your databases, especially the system ones. I can't even begin to imagine how catastrophic this would have been without good backups to restore from. Losing all our agent jobs, logins, replication, etc. . . (shudders)

    The only other bit not covered below, is that the SQLServerAgent service would not start after following these procedures. Nothing in SQL error log, no SQLAGENT.OUT file, nothing in event log, it was bizarre.

    On our system, the agent runs under a domain account. Not sure why, but some permissions were lost as a result of performing this procedure. After adding it back into the local groups below (local groups on the machine), all was well:

    SQLServerMSSQLUser$YourMachineNameHere$MSSQLSERVER

    SQLServerSQLAgentUser$YourMachineNameHere$MSSQLSERVER

    Rebuild system databases procedure:

    https://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx#Anchor_1

    Restoring the Master database:

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

    Restoring the model, msdb databases:

    https://technet.microsoft.com/en-us/library/ms190749%28v=sql.105%29.aspx

Viewing 5 posts - 1 through 5 (of 5 total)

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