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 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.
- Log in to the SQL server as the administrator > go to "Administrative Tools" > "Services", right-click the service "SQL Server (MSSQLSERVER)" > click "Stop".
- Double-click "SQL Server (MSSQLSERVER)" to open the "Service Properties" window.
- Click the "General" tab, and in the "Start parameters" field, type: -c -m.
- Click "Start" in the "General" tab to set the server in single user mode, and then click "OK" to close the dialog box.
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.
- Log in to the SQL server as the administrator.
- Go to Administrative Tools > Services, right-click on the following services, and click Start:
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.
- Repair corrupt MDF and NDF files of SQL Server database.
- Recover deleted records from MS SQL Server database.
- Recover corrupted SQL server database components: tables, triggers, indexes, keys, rules, and defaults.
- Export MS SQLdatabase to CSV, HTML, XLS, and MDF.
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.
Related Articles
- How to Repair SQL Server 2012 Step by Step
- How to Export Data from SQL Server to Excel Automatically
- SQL Error 233 No Process Is on the Other End of the Pipe
- [Solved] MS SQL Server Database Corruption Repair