Summary – When a SQL Server fails or crashes because of corruption in SQL database (MDF) file, the database components — tables, triggers, indexes, keys, rules, stored procedures and more — become inaccessible and result in server downtime. This results in loss of data and revenue, and therefore affects the business at large.
In such an instance, SQL Server administrators need to get back the SQL Server up and running by the earliest and with all the database components intact.
One of the ways to revive a failed or crashed SQL Server (due to corrupt or damaged MDF file) is restoring SQL database backup by using Microsoft SQL Server Management Studio (SSMS). Another solution is to repair the damaged MDF file. Read on to know the details…
Introduction
You can restore a SQL database backup by using SQL Server Management Studio (SSMS), which is a free software application to configure, administer, and manage all components of Microsoft SQL Server.
It is ideal to restore SQL database backup with the help of SSMS, however, the backup should not be corrupt. The reason to use SSMS is that the restore task is easy with SSMS. Plus, the Management Studio allows to restore the SQL database from ‘Database’ and ‘Device.’
Using the Device option, you can easily restore the SQL database from the backup (BAK) file in addition to the backup device, backup tape, and backup file location. Further, you can restore Full, Differential, and File/Filegroup backup.
Let’s say the SQL Server 2016 deployed in your organization has failed due to database (MDF) file corruption. Thereby, the database components are not accessible. However, you have a SQL database backup (BAK) file that you created a while ago. Now, you can restore the BAK file with the use of Management Studio to get back the MDF file. However, you will not get back the data that was added after the backup was created.
Step-by-step process to restore SQL database backup by using SSMS
- Login to the SQL Server in which you want to restore the database.
- Open SSMS and in its Object Explorer, connect to an instance of the SQL Server Database Engine and then expand the instance..
- Right-click Databases and select Restore Database….
- In the ‘Restore Database’ dialog box that appears, select the General, Options, or Files page.
- In ‘Source for restore’ section, select From device.
- Click the browse/ellipses (…) button to open the Select backup devices dialog box.
- In Select backup devices dialog box set Backup media as ‘File’ and click Add to open ‘Locate Backup File’ dialog box.
- In the Locate Backup File dialog box, select the SQL backup (BAK) file that you want to restore.
- After selecting the SQL backup BAK file, click OK button to return to Restore Database dialog box — a Restore dialog box having Options Page, General Page, and Files Page — to restore SQL database.
- In the Restore Database dialog box, the backup file and its location gets enlisted in the ‘Source’ section whereas, in the Destination section, the Database box gets enlisted with the database whose name you can change.
- Finally, click OK to restore the SQL database.
What if SQL database backup fails to restore with SSMS?
The purpose of restoring an SQL database backup by using SSMS is to get back the MDF file and all the database components. This is essential to make the failed SQL Server up and running.
However, if you fail to restore the SQL database backup in the first instance, you can repeat the process with the copy of the backup file that you created before starting the restore process. But in such an instance the probability is that the backup file was corrupt.
Therefore, the other way to deal with a failed SQL Server is to repair the damaged MDF file and retrieve its components. For this, you can run either the SQL Server DBCC CHECKDB command or an MDF file repair software – Stellar Phoenix SQL Database Repair.
Conclusion
SSMS is a free, easy-to-use tool that you can use to manage SQL Server, including the restoration of SQL database backup. Still, SQL database repair software is the recommendation.
Reasons are that with Management Studio, you may fail to restore SQL database. Next, it is available for SQL Server 2005 and above versions only. Also, as the restore process is lengthy, the SQL Server downtime is bound to increase.
On the other hand, Stellar Phoenix SQL Database Repair – a software popular for its quick, easy, and effective MDF file repair capability – surpasses these limitations. Plus, it gives better results in comparison to DBCC CHECKDB command.
Therefore, if you want to repair MDF file, so that the SQL Server works uninterruptedly, the better option is to employ a SQL database (MDF) file repair software that supports SQL Server 2016, 2014, 2012, 2008, etc. and provides several benefits.
References
https://www.stellarinfo.com/sql-recovery.php
https://campus.barracuda.com/product/backup/doc/15892599/how-to-restore-a-microsoft-sql-database-to-a-point-in-time/
Author Bio
Rajan Singh, a tech consultant with 5 years+ experience in RDBMS, Mobile Platform & AI. He likes to write troubleshooting guides to fix data access problems over the network using different API & third party libraries.
loading...
Last Post: How to Find the Mode in a Binary Search Tree?
Next Post: How to Clone Variables (The Clone Function) in Javascript?
