Rebuild SQL Server System Databases – Ways to Rebuild MSDB

Backup database on regular interval of time is important but many DBA’s do not bother backup system databases. Though user databases must backup on scheduled basis but it is rather important creating backup for system DB too. To rebuild SQL Server System Databases, users need to understand a few more things here.

Accidental deletion of the data from the system database is quite common. In such case, it will be difficult to recover the same. This important data may include linked server information, login details or other essential system objects.

Table of Content

• Reasons to Rebuild SQL Database
• Understanding Critical Prerequisites
• Quick and Advanced Solution for Users
• SQL Server Configuration Manager Method: SOL – 1
• Backup Method to Rebuild SQL Server Database: SOL – 2
  • Rebuild SQL Server System Databases from Setup
  • Overwrite Database from Template
• Restoring the Data Files from the System Database: SOL – 3
  • Reconnecting Databases
  • Restoring System Objects
• Best Practices For A Safer Process
• Conclusion

To prevent data loss under such scenarios, it becomes vital to rebuild system databases in SQL Server to ensure regular execution of processes. The SQL database might become inaccessible due to hardware or software failure or discrepancies. In such cases, it might require deploying a new Server and further restoring databases; or another option is to clone the particular instance.

Reasons to Rebuild SQL Server System Databases

Users can have various reasons to rebuild their databases. Let’s have a look at them. Several scenarios under which requires rebuild master database in SQL Server are summed up in this section:

• The master database is in a healthy state and backup is also available, but the major requirement is to bring the backup to known state.
• Now, second, database turns to unfeasible mode but no need to worry as the backup is available.
• Finally, the database is out of order and no backup is available for recovery. No worries, we can still get the instance running, but it really demands lot of hard work to bring the database to similar appearance that it once got.

Important Note: It is of immense importance to backup MSDB as well as model databases as the instructions mentioned herein may overwrite the respective database files destructively. This will also affect the components that are in healthy mode.

SQL Server Rebuild Master Database Critical Prerequisites

Basically, there are several things that users need to be aware of prior to entering this process. Therefore, perform the following steps before you start your database rebuilding process.

• Recording the SQL Server configuration values is the very first step here.

  select * from sys.configurations ;

• Maintain a proper record of the location for the log & data files of SQL Server databases. The motive behind this is to know the location of the files after rebuilding the database as users need to move the files to their original location by default.
• Know the location of the backup file for the master database.
• Take all the permissions & access to rebuild the master database. Only the member of sysadmin fixed server role are allowed for the same.
• Cross-check if your local server has a copy of the log, master, & model database files. The templates files are critical as the system take them in use for the rebuilding process. Below is the location of these template files.

  C:\ProgramFiles\MicrosoftSQLServer\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates

Rebuild and Restore Master Database in SQL Server – Solution #1

Master database restoration is quite complicated and tricky as well. It comprises of the information and details about all databases existing in the current instance and requires startup. In this scenario, it is important to start with single-user mode by using ‘-m’ flag. For this, follow the here-mentioned instructions for rebuild system database process:

1. Launch ‘SQL Server Configuration Manager’.
2. Right click on ‘SQL Server Service’.
3. Click on the ‘Properties’ and then select ‘Startup Parameters’ tab.
4. In the available box, type ‘-m’ and click on ‘Apply’.
5. Afterwards, restart SQL Server to apply modifications.

Further, with single user mode, database can be rebuild from backup through command line interface. Locate ‘sqlcmd.exe’ and further run the same. While using named instance, it requires to use ‘-S’ flag; whereas in case, if no reliable network exists, try running ‘-U’ and ‘-P’ flags.

Afterwards, remove ‘-m’ flag from the ‘SQL Server Service’ Startup Parameters tab. Further, try running SQL Server using multi-user mode.

Rebuilding SQL Server Database from Backup -Solution #2

If SQL database is not available in accessible mode then first of all, it is essential to create one for further execution. There are different theories exist for rebuilding SQL database as per application version.

• For SQL Server 2005 and 2008 editions, setup can be utilized for recreating existing system databases in batch.
• For SQL Server 2008(R2) and latest editions, template feature can be used for overwriting the  database.

Both the concepts are comprehensively discussed in the upcoming section.

Rebuild SQL Server System Databases from Setup:

Launch Bootstrap directory and type the given command:

"C:\Program Files\Microsoft SQL Server\<SQL version>\Setup Bootstrap\<Server release>"

Then execute below mentioned command for replacing the instance name, Windows account for acquiring administrative rights along with password for Server authentication.

“.\setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance_name> /SQLSYSADMINACCOUNTS=<admin_account> /SAPWD=<sa_password>”

Here, it is important to ensure that the replica for the msdb and model databases is available. Now, when the database has become obtainable, it can further be recovered from the available backup.

Overwrite Database from Template:

If the msdb as well as the model databases are in normal condition, then it only requires copying  database template files manually rather than rebuilding entire three system databases. Further, msdb and model database files can be restored from backup.

Template files can be found by following the given path in the directory but it solely depends upon the SQL Server version and the existing instance name:

"C:\ Program Files\ Microsoft SQL Server\ MSSQL<version>.<instance_name>\ MSSQL\ Binn\ Templates"

Afterwards, Rebuild SQL Server System Databases from good backup by using instructions available in the above section. If backup is not available, then keep reading to acquire possible resolution for getting access to lost database.

Reconstructing Data from SQL Server System Database -Solution #3

If the workaround procedures present in foremost section do not help much, users must follow the instructions for rebuild master database in SQL Server. It ensure that the replica for other databases are available. These copies can help if the files get overwritten during the  rebuild operation.

This is mandatory to have the existing instance running, if the sql database backup is available and is restored. In the absence of BAK files, there is still lot more to do in order to rectify the existing situation.

Reconnecting databases or restoring system objects might help and the procedures to implement these proposed tasks are given below:

Reconnecting Databases:

Using Admin Account information as well as SSMS details, try connecting to the SQL Server. It might be noticed that the enlisted database tree is displayed empty and this occurs because it contain location information for integrated databases which are not available at this instance of time.

But, don’t worry, all the incorporated databases are still there but it just requires telling SQL Server about the location for all the databases. In case, if backup is not available, the next task involves recording current storage location for all the existing databases along with the transaction logs and then attaching them manually. This can be done by using the following command:

Alternatively, the respective task can be done through ‘SQL Server Management Studio’:

1. Right click on the ‘Databases’ tab and then select ‘Attach Databases’.
2. In the ‘Attach Databases’ window, click on the ‘Add’ button.
3. Selecting MDF file will automatically locate the corresponding NDF as well as LDF files.

This is how the restore SQL Server database from MDF file can be carried out using SSMS.

Restoring System Objects:

Logins, end points and linked databases are some of the system objects existing in databases and involves the possibilities of data loss while rebuilding or restoring from template.

If in case, similar objects present in alternate existing instances; users can use SSMS to design ‘create script’ for editing and using on the restored Server. These scripts will save the precious time even if the system objects are not similar to that of other alternate instance.

Best Practices to Rebuild System Databases in SQL Server Safely

Here are some of the best practices that will help with the smooth execution of the master database rebuilding process in SQL Server.

• Keep regular backups of the SQL Server databases, as they can be helpful in various situations, like the backup and restore in SQL Server to restore the database.
• Next is to document all the crucial data from the database. This data can be:
  • The SQL Server logins and permissions.
  • Linked servers to the specified database.
  • The SQL Server agent jobs of the database.
  • Credentials, endpoints, and certificates in the database.
• Before initiating the rebuild system database in SQL Server, check and verify the SQL Server version and edition to avoid any issues. 
• When proceeding with the process, it is important to run the SQL Server database in single-user mode to avoid any interference during the process. 
• Avoid running the process directly in the production environment. Any mistake can lead to bigger challenges and issues in the database. It’s better to test the process in a testing environment first.

Conclusion

Although a number of potential workaround measures are available to Rebuild SQL Server System Databases even if the backup is not available; it is important to schedule backup at regular intervals. This will prevent data loss and will save spending hours in execution of restoration process.

Now, we all are aware of the SQL database rebuild and restoration process in the above content, considering variety of factors. However, there must be a solution available that is ready to deploy if the DBA can’t afford to spend long time span on recovery operation.