By: Tim Ford | Comments (3) | Related: 1 | 2 | 3 | > Disaster Recovery
Problem
Previously in this series about how to rebuild the master database, we itemized the steps required to prepare for rebuilding the master database. In the third tip in the series will present how to restore your SQL instance back to the state it was in prior to the rebuild process. In this tip, it presents what is involved in destroying your instance which is really what a rebuild does, believe it or not. I know that is a fairly-severe description of what you're about to do, but this is what it feels like when you're just about to hit the F5 key that executes that first query that detaches all your user databases: "WHAT AM I DOING!?"
Hopefully this tip takes some of the fear out of what you're about to do.
Solution
Before getting started there are a few prerequisites that you will need.
- Scripts that were created from the first tip in this series
- The media that was used to install SQL Server the first time
| Task | Steps |
| Detach all User Databases | Using the script saved as 0.sql from the first tip in this series, you will first need to detach all user databases.
When the master database is rebuilt, it has no metadata that corresponds to any user databases or objects on the instance. By detaching the user databases before the rebuild it assures a clean rebuild and removes any chance of contention on the files just in case something goes awry in the rebuild process. Execute 0.sql. |
| Load Media | Load the necessary media that corresponds to the edition and platform for the SQL instance that you're preparing to rebuild. This can be loaded directly into the DVD drive of your workstation/server, or copied locally from a file share somewhere on your domain. |
| Open Command Prompt and Initiate Rebuild Process | Open command prompt then run the following command, substituting the required values for your instance:
start /wait <path>\setup.exe [/qb | /qn] {VS=<VSName>} INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=<Current_Domain_User_Password> SAPWD=<New_Password> SQLCOLLATION=<NewSystemCollation>
Examples:
Example 1: Rebuild clustered instance of SQL Server 2005 with the following specifications:
c:\>start /wait C:\temp\MSSQL.1\setup.exe /qb VS=CLUST1 INSTANCENAME=NODE1 REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=DBA1pwd SAPWD=!grac3undrpre$$ure! SQLCOLLATION=SQL_LATIN1_GENERAL_CP1_CI_AS Example 2: Rebuild non-clustered, named instance of SQL Server 2005 with the following specifications:
c:\>start /wait C:\temp\MSSQL.1\setup.exe /qb INSTANCENAME=NODE1 REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=DBA1pwd SAPWD=!grac3undrpre$$ure! SQLCOLLATION=SQL_LATIN1_GENERAL_CP1_CI_AS Example 3: Rebuild non-clustered default instance of SQL Server 2005: c:\>start /wait C:\temp\MSSQL.1\setup.exe /qb REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=DBA1pwd SAPWD=!grac3undrpre$$ure! SQLCOLLATION=SQL_LATIN1_GENERAL_CP1_CI_AS |
| Backup System Databases | Once the rebuild is complete, take the time to backup the master, model, and msdb databases. I strongly suggest not overwriting the previous backups for these databases from prior to the rebuild until you're absolutely sure that the rebuild was successful. |
| Apply All Patches and Security Updates | The rebuild process takes your system databases back to the RTM (Release-To-Manufacturing) patch set level. Take the time now, to apply all necessary service packs and security patches to bring you back to the level you were at prior to the rebuild process. |
| Backup System Databases | Some call it overkill, I call it being a DBA: backup those system databases once again now that they are at the patch level that corresponded to the level pre-rebuild. |
| Follow Process in Final Tip in Series to Re-Establish All Instance Specific Content | The final tip in this series will step you through the execution of all the scripts created in Part 1 against the rebuilt instance. The end result is your instance returned to you in a pre-rebuild state only with the server collation you needed originally. |
Next Steps
- The final tip in this series will walk you through the steps necessary to re-attach your user databases and recreate all user-based content that existed prior to the rebuild. This is the post-rebuild process in a nutshell.
- Read Part1, Part3
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
