This blog post a Guest Post from one of my friend Priyanka Chouhan.
Priyanka Chouhan is a technical writer in “Stellar Data Recovery “with 5 years of experience and has written several articles on SQL. She has the fine knowledge of SharePoint and SQL Server. In the spear time she loves reading and gardening.
Here is the her blog about - Solutions for SQL Server Master Database Corruption
Master database is a backbone of the SQL Server database. It is a must have database and user cannot start the SQL Server without master database. It contains the all information about server configuration. When a user installs a SQL Serverthen, it usually creates master, model, MSDB and TEMPDB system databases by default. MSDB and TEMPDB depend on the version of SQL Server database. All system databases performs the different task like: master database is used to store all system level information, TEMPDB stores the information about temporary tables and temporary stored procedures, model is worked as a template for all system created database and MSDB is used by the SQL Server Agent to schedule alerts and jobs.
Corruption in the master database is not a new thing. I will advise to all database users to take the backup of master database because without master database, SQL Server cannot be started. If corruption is high in the master database then SQL Server does not allow starting the database. In this case user can rebuild it by using command prompt or restore from the latest backup (Scroll down to see detailed information). In case of minor corruption, it might be possible that user may start the database but SQL server does not allow to access the details inside the database.
These are the steps to restore the master database from the backup:
• Start SQL Server Configuration Manager then click on the SQL Server Services.
• Stop SQL Server Agent (it might connect first and prevent you to connect as a second user).
• Right-click SQL Serverandthen click Properties.
• Advanced tab, in the Startup Parameters box, insert –mto start the SQL Server in the Single User Mode.
• Now Start SQL Server.
• Start cmd.exe from start menu
• Type SQLCMD on command prompt
• Restore the master database using command:
RESTORE DATABASE master FROM DISK = ‘D:\MyFolder\master.bak’ WITH REPLACE
• Now remove the startup parameter –m.
• Start SQL Server.
This is the best way to fix master database corruption issues. If backup of corrupt master database is not available then user can easily rebuilt it by following below steps.
Rebuild the system database means we are going to drop the re-create in their real location.
• Open the command prompt and change the directories to the location of setup.exe file on the local server. Its default location in the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.
• Enter the following command in the command prompt Setup
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Instance_Name /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=Collation_Name]
• Once the rebuild process completes then it returns the command prompt without any message. User can view the Summary.txt log file to verify the process. The default location of theSummary.txt log file is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.
About Author:
Priyanka Chouhan is a technical writer in “Stellar Data Recovery “with 5 years of experience and has written several articles on SQL. She has the fine knowledge of SharePoint and SQL Server. In the spear time she loves reading and gardening.
Here is the her blog about - Solutions for SQL Server Master Database Corruption
Master database is a backbone of the SQL Server database. It is a must have database and user cannot start the SQL Server without master database. It contains the all information about server configuration. When a user installs a SQL Serverthen, it usually creates master, model, MSDB and TEMPDB system databases by default. MSDB and TEMPDB depend on the version of SQL Server database. All system databases performs the different task like: master database is used to store all system level information, TEMPDB stores the information about temporary tables and temporary stored procedures, model is worked as a template for all system created database and MSDB is used by the SQL Server Agent to schedule alerts and jobs.
Corruption in the master database is not a new thing. I will advise to all database users to take the backup of master database because without master database, SQL Server cannot be started. If corruption is high in the master database then SQL Server does not allow starting the database. In this case user can rebuild it by using command prompt or restore from the latest backup (Scroll down to see detailed information). In case of minor corruption, it might be possible that user may start the database but SQL server does not allow to access the details inside the database.
Restore from the Backup
These are the steps to restore the master database from the backup:
• Start SQL Server Configuration Manager then click on the SQL Server Services.
• Stop SQL Server Agent (it might connect first and prevent you to connect as a second user).
• Right-click SQL Serverandthen click Properties.
• Advanced tab, in the Startup Parameters box, insert –mto start the SQL Server in the Single User Mode.
• Now Start SQL Server.
• Start cmd.exe from start menu
• Type SQLCMD on command prompt
• Restore the master database using command:
RESTORE DATABASE master FROM DISK = ‘D:\MyFolder\master.bak’ WITH REPLACE
• Now remove the startup parameter –m.
• Start SQL Server.
This is the best way to fix master database corruption issues. If backup of corrupt master database is not available then user can easily rebuilt it by following below steps.
Rebuild System Database
• Open the command prompt and change the directories to the location of setup.exe file on the local server. Its default location in the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.
• Enter the following command in the command prompt Setup
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Instance_Name /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=Collation_Name]
• Once the rebuild process completes then it returns the command prompt without any message. User can view the Summary.txt log file to verify the process. The default location of theSummary.txt log file is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.
Nice Post
ReplyDelete