Restoring Database in SQL Server using .mdf and .ldf Files

SQL is one of the most common programming in use for server and database. It can be used with any RDBMS to manipulate SQL database over the server.

When managing a database, you may have the need -- at some point in time -- to restore it. Instead of a direct restore, you can use the .mdf file or .ldf file for this purpose.

.mdf or Master Database File is of prime importance since it stores all the data of the users. Likewise, the .ldf is a log file that records all the transactions performed on the primary database.

Restoring Database from .mdf or .ldf Files

If you want to restore database from the .mdf or .ldf file instead of direct restore, here’s what you need to do:

• First, you’d have to attach a database which later detaches easily. If you attach a database that doesn’t detech, it will result in an error.
• Next, maintain the .mdf or .ldf file inside a particular folder before you attach.
• Make sure you locate .mdf and .ldf files in the same location.

You can do this in two ways, either by SQL Server Management Studio or using T-SQL. Let’s look at each process.

Using SQL Server Management Studio

If you’re to use the SQL Server Management Studio, here are the steps you need to follow:

• Open MSSQL Studio
• Click on the dropdown option ‘Databases’. It will reveal the list of databases available.
• Identify the database you want to restore, right-click on it, and then click on Tasks >> Detach
• Next, you’d have to select a location to put .mdf and .ldf files
• So right-click on the database and then click on Tasks >> Restore
• When a new window appears, click on files
• Locate the ‘Restore as’ column and take note of the location. This is where the .mdf and .ldf files are located
• Navigate into the location and delete both the files
• Now, paste the new files in this location
• Now, from MSSQL Studio, right-click on databases drop down, and then select Attach
• In a new window, click on Add and then the required .mdf file
• Lastly, click on OK

Using T-SQL

If you’ll be using the T-SQL, its even more easier. Here are the steps to follow:

• Log in to SQL Server database engine
• Go to New Query Window
• Execute the following command:

In the above command, you created a database and attached it.

So that’s how you restore database in SQL server using .mdf and .ldf files.