Databases often crash and end up in restore.

Restoring a database from a .BAK file is a simple task from the MS SQL management studio. However, restoring the database using .mdf and .ldf can be confusing for the database user.

At Bobcares, we often get requests to restore an MSSQL database as a part of our Server Management Services.

Today, let’s have a look at how to restore a database using .mdf and .ldf files and see how our Support Engineers fix it.

Knowing about .MDF and .LDF files

Let’s begin by checking more about the .df and .ldf files.

MDF file is the primary data file that contains all the data and schema. LDF file is the Log data file and contains all the database transaction logs.

Usually, the database backup file will be in .bak format. And, it can be restored pretty easily.

In some cases, the .bak file may not be available, but MDF and LDF files will be present. We can still restore the database from the .MDF and .LDF files.

How to restore a database with MDF and LDF file

Recently, we had a customer who was confused about how to restore the database from the files. Let us discuss how our Support Engineers helped him to restore the database from the available files.

We Open MS SQL Management Studio >> Right Click on Databases >> Tasks >> Detach
After Detaching the database, we start restoring the database.
Right-click on the Database >> Tasks >> Restore
Browse the location of your MDF and LDF files to attach the files. Validate the file and click OK.

Finally, our Support Engineers will attach the database back. To do this, we
Right-click on the Databases >> Tasks >> Attach.

A common error during restoration and how we fix it

Let us discuss how our Support Engineers fix the common errors faced during Database restore.

1. Restore failed for server “database name”

One of the common error during database restore is “Restore failed for server database name.” Usually, this error pops up after the final steps. The database files will not be able to overwrite the existing files.

To resolve the error, we create a new database with a different name and attach the MDF and LDF files. Also, the MDF and LDF files need to be renamed to a different name before restoring the files,

2. No files are shown

Another common error faced when trying to add the MDF and LDF is the files do not show up in Windows Explorer. This happens mainly when the SQL user does not have permission to view the content in the folder.

When placing the MDF and LDF our Database Engineers always make sure to place the file in the location where SQL user has permissions. It is advisable to place the files in the SQL configuration folder. Here the SQL user has all the privileges in the folder. As a result, there will not be any permission errors too.

[Need assistance to fix MSSQL error? We’ll help you.]

Conclusion.

In short, we discussed how our Support Engineers restore the database using the .MDF and .LDF file. Together we checked the common errors that arise when restoring a database from MDF and LDF files.