Skip to main content

SQL Server- Guest Post - Solutions for SQL Server Master Database Corruption

This blog post a Guest Post from one of my friend Priyanka Chouhan.

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


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.

Conclusion:

These are the methods to solve the corruption in the master database. Sometimes antivirus software can be the reason of master database corruption. So read the guidelines here to choose the antivirus https://support.microsoft.com/en-us/kb/309422/en-us



Comments

Post a Comment

Your feedback is very important to me. Please provide your feedback via putting comments.

Popular posts from this blog

How to create Rest API(Web API) with ASP.NET Core 1.0

Recently Microsoft has released ASP.NET Core 1.0 RC2, I am getting lots of request from readers that about creating Web API. So I thought it will be a good idea to write a blog post about how to create Rest API(Web API) with ASP.NET Core 1.0.

So let’s get started via creating an ASP.NET Core 1.0 Web Application like following.



Once you click on ASP.NET Web Application, It will ask whether you need to create Web Application or Web API application. We are going to Web API so I am going to select Web API Application like following. Please note that in ASP.NET Core 1.0 there is no separate libraries or DLLs required for creating web APIs. This is just a project template.



Now once you click on OK It will create a Web API application with default values controller and program.cs.  As you know Program.cs is now starting point for the ASP.NET Core 1.0 application so It contains all the required configuration and startup items. Following is a code for that.
using System.IO; using Microsoft.Asp…

How to use migration with Entity Framework Core

Entity framework core is  the lightweight, extensible and cross-platform version of Entity Framework. Before some time, Microsoft has Released a new version of Entity Framework RC2. I have written a couple of blog post about Entity framework code first migration earlier for Entity framework 6.0. So there was a couple of request coming for writing a blog post about Entity Framework Core RC2 migration. So I thought it will be a good idea to give an overview how database migration works in Entity Framework Core RC2. This post will cover a basic scenario where we are going to create the database with existing ASP.NET Identity migration and then we are going to create a new model and have that migration applied in the database.

How to use Entity Framework Migrations: Let’s get started, To demonstrate entity framework core migrations, I am going to create a sample asp.net core web application like following.



Once we select asp.net core application it will appear the following dialog.



Now w…

Creating PDF with ASP.Net MVC and RazorPDF

Update: I have written a new blog post about better approach to create a PDF with asp.net mvc- You can find that following location.- A Better Solution to create PDF with Rotativa and ASP.NET MVC
In this post we are going to learn how we can easily create PDF from ASP.Net application with the help of Razor PDF NuGet package.

About Razor PDF:
This NuGet package is created by Al Nyveldt It internally uses ITextSharp an open source PDF convertor library. RazorPDF uses Razor View engine to create iTextXML which is in tern used to produce PDF file. You can get more information about that at below link.

https://www.nuget.org/packages/RazorPDF

Example(Creating PDF with ASP.Net MVC):
So what we are we waiting for ? Let’s create a simple example. To create example first thing we need to a create and ASP.Net MVC application.