Restore damaged Master database
I am convinced that most netizens see this title, they will "far away"! However, as a similar database administrator, how can we avoid it? Face the problem, we still have to go face it and think about it. I believe: There will be beautiful rainbow after the wind and rain!
Master databases that have been damaged or wrong are one of the most difficult solutions. The Master database contains key information required for SQL Server. If the Master database is wrong, then SQL Server can't start. To restore the Master database in this case, you can follow these steps to recover from the disaster:
1. Reconstruct the master database using the Rebuidm.exe file under the / program files / microsoft sql server / 80 / tools / binn directory. Rebuilding the Master database allows your database files. In case, it is a good idea to back up data and log files to a separate directory.
2. Start SQL Server using the -m parameter so that SQL Server can be restarted in a single user mode.
3. Restore the Master database from the most known best backup.
4. Verify that the Master database has been successfully restored: Confirm that all databases have returned to normal operation. Restore the MSDB database from the most known best backup.
5. Stop and launch SQL Server in normal mode.
6. Open the database to the production user.
If this is done, reconstruct the Master database and then attach this database. This is also one of the tasks of the DBA, of course, you don't want to do this. If there is a problem with the Master database, there is a problem with all databases on the server.
In general, the first step to complete when restored is to install a new Master database, so that the server can start. On this basis, you can restore the real Master database. After you click RebuildM.exe, you will start the Rebuild Master utility, it requires three parameters:
1 Server name to be restored.
2 Master database in the original installer location, if the version of SQL Server is SP2, this location should be the X86 / Data directory of the installation CD (in the earlier version, due to the existing program error, it is possible to need this DATA Directory copy to your hard drive or network drive).
3 Server sorting rules.
This reconstruction process will recreate all four system databases and Northwind and PUBS, followed by configuring the server. The entire reconstruction process takes about a few minutes. If you have upgraded the server using Service Pack, it is possible to take longer.
The second step of the recovery process is to start the server using server management or enterprise manager.
Depending on your security configuration, the Enterprise Manager is likely to not connect to the server. Rebuilding the Master database will drop all users other than SA login, and reset the empty interface order of the SA. If you want to adjust the connection configuration of the Enterprise Manager so as to connect to the server as SA, you can edit the registration attribute of SQL Server. Don't worry about the empty password of the SA, because we will soon recover the true Master database.
Once the server runs again, you will find that all user databases are gone, because the reconstructed Master database does not contain information about these user databases.
The third step of the recovery process is to place the server in a single user mode to restore the Master database. In the Enterprise Manager, complete the following steps:
1 Select the server.
2 Use the right-click to select the "Properties" menu item from the pop-up shortcut menu, open the Server Properties dialog. 3 Click the "Startup Parameters" button.
4 Enter -m in the "Parameters" text box, then press the "Add" button.
5 Press the "OK" button to turn off the Startup Parameters dialog box and close the Server Properties dialog.
6 Close the business manager.
Warning: Do not use the Enterprise Manager when the SQL Server instance is in a single user mode. Doing so may lock this example so that it cannot accept the status of other connections.
7 Use the SQL Server Server Manager to stop and restart the SQL Server instance.
The fourth step of recovering damaged Master database is the real restore. Because SQL Server is now in single user mode, you must use the query analyzer to complete this step. If you try to restore the Master database in the Enterprise Manager, you will have a serious problem. When restored in the query analyzer, you need to perform the following command:
RESTORE DATABASE MASTER
From
Disk = 'systemBackup'
WITH file = 1
The result is as follows:
The master Database Has Been successfully rest.
SHUTTING DOWN SQL Server.
SQL Server IS Terminating this process.
Turn off the query analyzer and restart the SQL Server instance using SQL Server's service manager.
The last step of the restored Master database is to remove the -m startup parameters joined in the third step and re-put the SQL Server instance in a multi-user state. To modify this startup parameter, you can use the Enterprise Manager's Server Properties dialog.
Turn off the Enterprise Manager, stop using SQL Server's service manager and restart the SQL Server instance.
