REBUILD THE master DATABASE
Step 1. Put SQL Server into Single User Mode
The first thing you will want to do is stop the SQL Sever Sevicer (sqlservr.exe) and the associated services (Agent, Full Text, Etc). There are a few ways to do this, but the easiest way would be to use the SQL Configuration Manager (Start->All Programs->Microsoft Sql Server 2005->Configuration Tools->Sql Server Configuration Manager). From this console you can manage the various SQL server services running on the machine. Right Click on each service listed and stop the service. The services are stopped, you can proceed to Step 2.
Step 2. Start the SQL server in Single User Mode
Open a command window and navigate to the folder where Sqlservr.exe resides (generally:\Program Files\Microsoft Sql Server\MSSQL.1\MSSQL\Binn). Run “sqlserver.exe -m” to start the sql server from single user mode.
Step 3. Rebuild the system databases
In SQL 2005, the rebuildm.exe program is nto supported. To rebuild the master database you need to use the setup.exe found on the SQL 2005 installation media. To rebuild, use the following command “start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=”. Please refere to Books on Line for compelete details on how to use Setup.exe.
Step 4. Restart the SQL Server Services in regular mode
End the command shell you started earlier. Ctrl-C to stop SQL server in single user mode, then close the command window. Go back to the Sql Server Configuration Manager and restart the SQL services.
------------------There is another approach for the above---------
Sometimes we can observer master database is in single user mode. We are unable to connect to the sql server. But master database backups are available. At this what we need to do is:
For example in A Server we are unable to connect to the sqlserver because master database is in single user mode. What we need to do is Copy the master.bak file to another server for example B server. There you restore the master.bak file with any other name not master because master is already there in that particular server. After restoring the master database with any other name. .mdf and .ldf files are generated. So copy those .mdf and .ldf files and paste it in the A server. Start SQL Server in Single user mode(Go to control panel Choose MSSQL ServerProperties Stop the services And by the time of restarting the services add –m in Startup parameter so that it starts in SingleUserMode.). Now SQL Server Started in Single User mode now attaches those .mdf and .ldf files to the server. Now master database is ready but you can find it as master (single user mode). Click on master database go to properties and change it from Single user mode to Multi-user mode. Now it comes to MultiUserMode.
After that again start the services normally in .services. Previously we start the services in single user mode by giving –m as startup parameter.
The first thing you will want to do is stop the SQL Sever Sevicer (sqlservr.exe) and the associated services (Agent, Full Text, Etc). There are a few ways to do this, but the easiest way would be to use the SQL Configuration Manager (Start->All Programs->Microsoft Sql Server 2005->Configuration Tools->Sql Server Configuration Manager). From this console you can manage the various SQL server services running on the machine. Right Click on each service listed and stop the service. The services are stopped, you can proceed to Step 2.
Step 2. Start the SQL server in Single User Mode
Open a command window and navigate to the folder where Sqlservr.exe resides (generally
Step 3. Rebuild the system databases
In SQL 2005, the rebuildm.exe program is nto supported. To rebuild the master database you need to use the setup.exe found on the SQL 2005 installation media. To rebuild, use the following command “start /wait setup.exe /qn INSTANCENAME=
Step 4. Restart the SQL Server Services in regular mode
End the command shell you started earlier. Ctrl-C to stop SQL server in single user mode, then close the command window. Go back to the Sql Server Configuration Manager and restart the SQL services.
------------------There is another approach for the above---------
Sometimes we can observer master database is in single user mode. We are unable to connect to the sql server. But master database backups are available. At this what we need to do is:
For example in A Server we are unable to connect to the sqlserver because master database is in single user mode. What we need to do is Copy the master.bak file to another server for example B server. There you restore the master.bak file with any other name not master because master is already there in that particular server. After restoring the master database with any other name. .mdf and .ldf files are generated. So copy those .mdf and .ldf files and paste it in the A server. Start SQL Server in Single user mode(Go to control panel Choose MSSQL ServerProperties Stop the services And by the time of restarting the services add –m in Startup parameter so that it starts in SingleUserMode.). Now SQL Server Started in Single User mode now attaches those .mdf and .ldf files to the server. Now master database is ready but you can find it as master (single user mode). Click on master database go to properties and change it from Single user mode to Multi-user mode. Now it comes to MultiUserMode.
After that again start the services normally in .services. Previously we start the services in single user mode by giving –m as startup parameter.
Comments