How To Restore SQL Server Database Backup

In this article, I will guide you on how to Restore SQL Server Database backup.

If you want to know how to take database backup please refer to my previous article. 

There are two ways to restore a database backup.

Method 1

Open SQL Server Management Studio (SSMS) and follow the below steps

Select the Database that you want to restore. 

For example, here I am using the BusinessSimulator database. 

Select Database. Right click on database -> select Task -> Restore -> Database.

Restore 

Once we click on Database, a pop-up window will open.

This window will show the Database name which we are restoring. Select the Device and browse the backup file.

Pop up 1

Pop up 1.1

Note: In my case my database backup file was "C:\WorkingProjects\Practice\DataBase\BackupsBusinessSimulator_20220725.BAK"

Back up file from path

Once the file is selected click on OK. you will see the file is browsed and added to the backup set to restore.

File added 

Now click on Files and map and set .mdf and .ldf of the database.

Note

  1. MDF - Master Data File,
  2. LDF - Log Data File

Database .mdf and .ldf file path

Database .mdf and .ldf file path

Browse and select the database .mdf file 

MDF file

Browse and select the database .ldf file 

LDF File

Click OK. Now, Select Options and check the close existing connection to the destination database and Overwrite the existing database (WITH REPLACE).  

Options

Now click OK, Database will get restored.

DB Restore

Method 2

Using SQL Query.

RESTORE DATABASE BusinessSimulator FROM  DISK = N'C:\WorkingProjects\Practice\DataBase\BackupsBusinessSimulator_20220725.BAK' 
WITH MOVE N'BusinessSimulator' TO N'C:\WorkingProjects\Practice\DataBase\BusinessSimulator_DB\BusinessSimulator.mdf',  
     MOVE N'BusinessSimulator_log' TO N'C:\WorkingProjects\Practice\DataBase\BusinessSimulator_DB\BusinessSimulator.ldf',  
RECOVERY, -- 'with recovery' is optional here - it's the default if not specified - database will be available
REPLACE;

Now execute the query,

Query

Once the query is executed, it will restore the database.