DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Cloud Database Services Compared: AWS, Microsoft, Google, and Oracle
  • What Developers Need to Know About Table Partition Pruning
  • 10 Database Optimization Best Practices for Web Developers
  • Shared Schema Strategy With Postgres (Part 1)

Trending

  • Unlocking AI Coding Assistants Part 2: Generating Code
  • Java’s Next Act: Native Speed for a Cloud-Native World
  • It’s Not About Control — It’s About Collaboration Between Architecture and Security
  • Rethinking Recruitment: A Journey Through Hiring Practices
  1. DZone
  2. Data Engineering
  3. Databases
  4. Rebuild System Databases in SQL Server

Rebuild System Databases in SQL Server

Learn about system database corruption and what it means. If corruption in system databases occurs, is a problem because it will cause the SQL Server to stop.

By 
Priyanka Chauhan user avatar
Priyanka Chauhan
·
May. 13, 22 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
6.1K Views

Join the DZone community and get the full member experience.

Join For Free

In this article, we will talk about system database corruption. It means that one or more system databases are corrupted. Corruption in system databases, such as master databases, is a big problem because SQL Server will stop. Let’s see how to fix this problem. 

About System Databases

System databases are databases used internally by SQL Server and are necessary for its operation.

There are five system databases: 

  • Master database: It stores system-level information of the instance of SQL Server. 
  • msdb database: It is used by SQL Server Agent for scheduling jobs. 
  • Model database: It is used as a template for the creation of a new database.
  • Resource database: It stores internal system objects that are associated with the sys schema of every database.
  • Tempdb database: It is used as a space to store temporary objects or intermediate result sets.

In the SQL Server Management Studio (SSMS), under the system database folder of the Object Explorer, you can find four of the five system databases.

Object Explorer

In particular, the master database is considered the “heart of SQL Server engine” as it contains information, such as processes, locks, remote accesses, etc. It also contains information about other user databases. If this database is corrupted, SQL will not start. 

SQL Server Engine No Longer Starts

We often notice that the system databases are damaged just because the database engine does not start. When we look at the SQL Server error log, this error appears:

 
Error: 17113, Severity: 16, State: 1.

Error 2 (The system cannot find the file specified.) occurred while opening file

'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf' to obtain configuration information at startup.


An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

The message indicates that the master.mdf file is not accessible.

In this case, we need to rebuild the system databases.

Rebuild System Databases

Note: The following procedure rebuilds the master, model, msdb, and tempdb system databases. We cannot specify the system databases to be rebuilt. The rebuild operation drops and recreates the system databases. Therefore, the changes made in the system databases will be lost and we need a backup to restore. 

Before starting this procedure, we need to check some things.

The rebuild procedure uses the system database template files. So, go to the \Binn\Templates folder and make sure that these files are present.

files in the \Binn\Templates folder

Next, locate the setup.exe file.

location of setup.exe file

Now, we need to change the actual path to the folder where the installation file is located. 

To do this, open a command prompt and type:

Cd C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019 

screenshot of command prompt

Now, we will execute the setup.exe file with some parameters. 

These are some of the parameters:

  • /QUIET — performs a silent installation
  • /ACTION — sets the value to RebuildDatabase
  • /INSTANCENAME — the name of the instance 
  • /SQLSYSADMINACCOUNTS — the name of the account (account must have the admin rights in SQL Server)
  • /SAPWD — provides a new password for SA login (enable SA account if it is disabled and set up with a strong password)
  • /SQLCollation — provides new collation name to SQL Server (optional)

 Now type the following command:

 
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<XYZ> /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=<PWD>


Remember to replace the <XYZ> string with the instance name and the <PWD> string with the password.

Press ENTER to execute the setup. 

If no error message comes out, the procedure is successful. You have recovered your instance and the SQL Server will be functional again. 

Restore Backups of Master and msdb Databases

Now our SQL Server is running again. However, keep in mind that after the rebuilding operation, changes made to the system databases will be lost. 

To get the system back to where it was, we need to restore backups of the master and msdb databases. 

To do this, start the SQL Server in single-user mode.

Open a command-line prompt and then start SQL Server with these arguments:

command-line prompt

The -s argument represents the name of the instance. 

With the -m argument, the SQL Server will only accept a connection if the application is SQLCMD.

Now that SQL server is in single-user mode, we can restore the master system database.

With the sqlcmd command, we can execute the T-SQL command from the command line.

After -S argument, write the correct instance name.

SQLCMD

Now we can restore the master database. For this, type the following command:

command for restoring the master database

When the restore process is complete, the SQL Server instance will shut down. 

Finally, start the SQL server instance as usual and then restore the msdb database.

Conclusion

Using the above process, we can solve most of the problems that occur due to system database corruption. However, there are some particular cases. For example, it could happen that the model database is corrupted. The model database is used as the template when you create a new database. 

Since the tempdb database is created each time SQL Server starts and the creation of the tempdb needs the model database, SQL Server will not start if the model database is damaged. In this case, we can try to start SQL Server with the trace flags -T3608 and -T3609 and then restore the model database from backup via T-SQL.

[Read more: How to Fix Recovery Pending State in SQL Server Database and Resolving SQL Database Stuck “In Recovery” Mode]

Database engine sql

Opinions expressed by DZone contributors are their own.

Related

  • Cloud Database Services Compared: AWS, Microsoft, Google, and Oracle
  • What Developers Need to Know About Table Partition Pruning
  • 10 Database Optimization Best Practices for Web Developers
  • Shared Schema Strategy With Postgres (Part 1)

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!