How to restore the SQL Server master database urgently

If the primary database fails, Microsoft's SQL Server may crash. Let's take a look at how to deal with such events and learn how to use the Enterprise Manager and query analyzer to repair the primary database.

As an administrator of Microsoft SQL Server, you must know how to fix a crashed primary database. The primary database stores your logon information and the most important pointer to all your databases. Without the primary database, you cannot start SQL Server successfully. In this article, I will show you how to fix the primary database in case of a crash, and tell you how to recreate the primary database if necessary.

Plan preparation

It is very important to develop a plan to respond to crashes and/or primary database faults. This will help you deal with the disaster in accordance with the established approach, rather than rush to respond under pressure. I have encountered many situations that are easy to panic, but I finally succeeded in all the difficulties due to my calm and correct solutions.

How can I know that your primary database has crashed?

Before officially starting to discuss how to fix and reconstruct the primary database in case of system faults, we need to first understand how to identify that the primary database has crashed. To illustrate this, I will break down a primary database and tell you what symptoms will happen when the primary database crashes.

Now let's assume that your company has encountered a surge, causing SQL Server to restart. During the restart, SQL Server does not start properly. If you view the Error LogFigure), You will see that the primary database crashes or is lost. Now that you know what information you need to view, let's see how to fix the primary database.

Figure

Fix your primary database

The first step to fix the primary database is to use the "Rebuild wizard Rebuild Wizard,Rebuildm.exe), which is placed in the \ Program Files \ Microsoft SQL Server \ 80 \ Tools \ BINN directory. Now let's take a look at how the reconstruction wizard works.

Double rebuildm.exe startupFigure BAs shown in the dialog box.

Figure B

In this dialog box, you can specify the restoration settings of the database server and the location of the original installed data file. To make this process easier and faster, we need to copy the x86 directory from the SQL disc to the hard disk and change the point to the local copy. Once all the information is verified, click "Rebuild )". Then the system prompts you to confirm the operation, as shown in figureFigure C.

Figure C

Click "OK )". Once the reconstruction process is complete, you will see a message indicating that the reconstruction is successful. Now you have a brand new primary database ready to repair.

First, open the command line prompt and enter \ Program Files \ Microsoft SQL Server \ MSSQL \ BINN \Sqlservr.exe-c-mCommand to start the SQL Server in single-user mode. The result is as follows:Figure D.

Figure D

After you start SQL Server in single-user mode, you can use the backup file to restore the primary database. You can use "Query Analyzer)" or "SQL Enterprise Manager" to fix it. If you use the query analyzerFigure ERun the query in the same way.

Figure E

If you are using an Enterprise Server, right-click the primary Database and select "All Tasks | Restore Database)" to view the location of your device, as shown in figureFigure F. Click "OK" twice to repair the master database.

Figure F

After the primary database is repaired, log out of single-user mode and restart SQL Server in normal operation mode.

If your repair operation cannot be completed successfully for some reason, try another method. You can simply re-create the primary database and add all the databases residing in the data directory. You can use the Enterprise Manager or query analyzer to add a database. In the Enterprise Manager, right-click "Database Databases)" and select "add Database Attach Database)", as shown inFigure G.Figure G

Conclusion

Now that you know how to successfully reconstruct the primary database in case of a disaster, you can add these technologies to the disaster recovery plan. In this way, you do not have to be overwhelmed when the SQL Server's primary database crashes and the database Server stops working.

(