Applied to MS SQL Server 2016, 2014, 2012, 2008, 2008 R2, and 2005
Master database file, namelyMDF file,is the main user database file that holds the primary configuration details and all the major data of SQL server.If the SQL server corrupted or damaged due to a unforeseen shutdown, crash, or a virus attack, the master database file will get affected. Once the problem occurs, restoring the MDF file is the ultimate approach to solve this problem.
When you need to restore master database in SQL server 2008 or 2012, you generally have two solutions. One is restoring SQL server master database from a backup file, and the other is restoring MDF file without a backup. Choose one of the two different methods below to solve the issue based on your situation.
If you have a backup of the SQL server master database, it's not complex to restore the MDF file.
RESTORE DATABASE master FROM WITH REPLAC
The "replace" command means the restoration process will continue even if there is a database with the same name, and the existing database will be removed.
When the master database restoration completes, the SQL Server instance will shut down. You have to remove the single-user startup parameter and set Microsoft SQL Server to Multi-User Moder before starting the Server again.
SQL Server (MSSQLSERVER)
SQL Server Agent (MSSQLSERVER)
SQL Server Reporting Services (MSSQLSERVER)
If you are new to SQL server and don't have sufficient knowledge SQL database, or if you have no backup created before, it is still possible to restore master database in SQL server by using the professional Qiling MS SQL Recovery. It can recover MDF file and its components (tables, triggers, indexes, keys, rules&stored procedures) as well as deleted records from SQL database.
Step 1. Stop MS SQL Server service via services.msc or Management Studio.
Step 2. Run Qiling SQL Recovery. In the main interface, choose the MDF/NDF file of the database you want to restore. Then click "Repair" to start repairing your MDF/NDFfile.
If you know the exact location of the file, click "Browse" to locate the database.
If you don't know the file location, click "Search" to search for the .mdf or .ndf file in.
Step 3. When it has done, you will see the recovered database objects listed in the left pane of the window.
Step 4. Click "Export" in the bottom right corner of the screen to save your database objects. Choose a preferred format, MDF, or SQL scripts.
On the "Export to database" window, choose "Create new database" or "Export to existing database" to save the repaired data. If you want "Create new database", enter the database name and choose an SQL location. If you select "Exporting to existing database", you need to select the existing database.
Step 5. Now restart the SQL Server.