SQL Restore Database from bak File

This section will explain the step-by-step approach for the SQL Server restore database procedure using the BAK file. For this Restore Database demonstration, we use the adventure works 2017 .bak.

SQL Restore Database from BAK File

In this example, we will use the BAK file to Restore the Database. The server does not have the AdventureWorks2017 database. Please refer to Backup for creating a backup and Maintenance Plan for making regular backups in Sql Server.

As you can see, the MDF and LDF corresponding to the Adventure Works 2017 is not there in C Drive.

MDF and Log files in system hard drive 2

We have an Adventure Works Backup file in our D Drive, and we will use this bak to restore a database.

Choose or Select the BAK File from System Hard Drive 3

Syntax of the restore Database from the backup file

RESTORE DATABASE DatbaseName
	FROM DISK = 'D:\Backups\BackupFileName.bak'
	WITH MOVE 'LogicalMDFFileName' TO 'location\MDFFileName.mdf',
	MOVE 'LogicalLDFFileName' TO 'location\LDFFileName.ldf'

Let me use the above syntax to restore the Adventure Works.

RESTORE DATABASE AdventureWorks2017
	FROM DISK = 'D:\Backups\AdventureWorks2017.bak'
	WITH MOVE 'AdventureWorks2017' TO 'C:\Program Files\Microsoft ...........\DATA\AdventureWorks2017.mdf',
	MOVE 'AdventureWorks2017_log' TO 'C:\Program Files\Microsoft .........\DATA\AdventureWorks2017_log.ldf'

The query executes without any errors, and the db is attached successfully.

Now you can see the Adventure Works 2017

The above process has automatically added the MDF and LDF to the C Drive.

Automatically saved MDF and LDF FIles in the File System 7

If you don’t know the logical names, then use the Restore File System to retrieve the Logical Filenames.

RESTORE FILELISTONLY 
	FROM DISK = 'D:\Backups\AdventureWorks2017.bak'

DATABASE RESTORE FILELISTONLY FROM DISK option

Restore Database in SQL Server using BAK Example 2

How to Restore Database using BAK file?. For this demonstration, we are going to use Management Studio to restore the database from the .bak file. As you can see from the above, the server does not have the AdventureWorks2017 database. Please check that the Mdf and ldf files corresponding to the AdventureWorks2017 are not there in C Drive.

Please refer to Backup Database for creating a database backup, and refer to Maintenance Plan for creating regular database backups in SQL Server. We have a Backup file in our D Drive, and we use this file to restore.

Restore Database in SQL Server using BAK File

Right-click on the Databases folder and select the Restore Databases… option.

Selecting the Restore Databases.. option, it will open the following window. In this example, we want to restore a database from the file system. So, let me select the Device option. Next, click on the … button will open the following window.

Click on the Add button and select the backup file by navigating to the file location.

Click OK to restore the AdventureWorks database

Please wait until the restore is finished. Once it finished the restoration, it. will open a pop-up window. Click OK to Restore the SQL Server Database using the bak file.

Now you can see the Adventure Works 2017 database in the Management Studio. The above process will automatically add the Mdf and ldf files to the C Drive.