June 2, 2016 at 12:40 pm
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.
June 2, 2016 at 12:55 pm
Do you have recent valid backups of these databases?
June 2, 2016 at 1:38 pm
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.
June 2, 2016 at 2:00 pm
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.
June 2, 2016 at 10:50 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply