[Solved] How to Restore Master Database in SQL Server 2008/2012 - Qiling  

How to Restore Master Database in SQL Server 2008/2012 with or Without Backup


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.

how to restore master database in SQL server

How to Restore the SQL Server Master Database with or Without Backup

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.

Solution 1. Restore Master Database in SQL Server from Backup File

If you have a backup of the SQL server master database, it's not complex to restore the MDF file.

Notice:
If you haven't created any backup of SQL server database, skip this method and use Solution 2 to restore master database in SQL server without backup file.

Step 1. Set your SQL server instance in single-user mode.

how to restore SQL server master database

Step 2. Run Transact-SQL command to restore SQL server master database.

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.

Step 3. Set Microsoft SQL Server to Multi-User Mode.

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)

Step 4. Restart the SQL Server instance and perform other recovery tasksif you want.

Solution 2. Restore SQL Server Master Database Without Backup File

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.

Select file types that you want to recover

Step 3. When it has done, you will see the recovered database objects listed in the left pane of the window.

select a location where you deleted files

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.

Recover deleted files

Step 5. Now restart the SQL Server.

Related Articles


Is this information helpful?     

What can we do to improve this information? (Optional)
Refresh Please enter the verification code!