By: Tim Ford | Comments | Related: 1 | 2 | 3 | > Disaster Recovery
Problem
Well, so far we've prepared for the rebuild of our master database and have gone through the surprisingly simple rebuild process. (One of those DBA things: spend all the time on preparation for something going wrong only to have the actual event you're preparing for to go off without a hitch!) Now we find ourselves at the point where we need to recover all we've lost in the process: the logins, the jobs, oh those databases too. I guess those may be a tad bit important too! Let's get to work, shall we?
Solution
At this point you should have already read the first two tips in this series:
- Rebuilding the SQL Server master database - Part 1
- Rebuilding the SQL Server master database - Part 2
In addition, if you have gone through the first two tips you should now have all of the materials ready in order to complete the process of rebuilding your master database and getting your server back online for your users.
If you have gone through part 1 and part 2, your master database should be rebuilt, but now you need to reattach your databases and rebuild the other items that are needed.
| Attach All User Databases | Now we finally move on to what matters most to the users - their databases. In our ramp-up to rebuild we created a script called 1.sql. Review it once again before running it - make sure that the master, msdb, model, and tempdb databases are not included, and then run it. Once completed, you're instance will be almost to the point where it needs to be, but you're not quite there yet! |
| Recreate Logins | In the first tip we ran a script where the output was a series of CREATE LOGIN... T-SQL commands that we saved as 2.sql. Well now is the time to load 2.sql and execute it. The end results are for the most part still going to be unusable at this point for any login whose default database is not set to a system database (since we've yet to attach any of the user databases). There is no need for concern since we're not anticipating users attempting to connect to the instance yet. |
| Restore Database dbo Aliases | Run the 3.sql script created in part 1 to restore dbo aliases in all your user databases. |
| Create Custom Objects in master and msdb Databases | It may seem out of order, but we're now going to run any scripts that were created during the last step we undertook in preparation for the rebuild. In that step we scripted out any custom objects that existed in the system databases. Since those are the only databases that now exist in our instance, we can safely re-run the scripts to re-create them. |
| Create SQL Agent Jobs, Operators, etc. | Continuing the theme of working out of order, you're now going to run any scripts you created previously that pertained to SQL Agent objects: Jobs and Operators in particular. You may note that Job Schedules were not included in this list. That is because those objects are created within the Job creation script if they do not already exist in the msdb database. |
| Restore System Databases' Securables | When the user databases are restored any securables are restored along with them. This is not the case with the system databases however. These databases are completely rebuilt and as noted previously, need to have some post-build work done to ensure they are in the same state they were in previously. Verify that 4.sql contains only the securables for the system databases and edit it for only those securables that you want to re-apply. (Now is a great time for cleanup!) When 4.sql is in the state you wish, execute it. |
| Review and Correct SQL Instance Service Accounts | Before turning the SQL instance back over to the users take the time to review the instance service accounts for not only the SQL Server service, but also the agent and browser services. Make changes accordingly and restart the services if necessary. It's best to do this now rather then discover an inconsistency or problem later when users would be impacted unnecessarily. |
Next Steps
- sp_MSforeachdb is thoroughly explained in this tip.
- For information concerning the system databases review this tip.
- Read Part1, Part2
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
