SQL Restore Database

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 using BAK File

In this example, we will use the BAK file to Restore the SQL Database. The server does not have the AdventureWorks2017 database.

TIP: Please refer to Backup for creating a backup and Maintenance Plan for making regular backups in Sql Server.

Open SQL Management Studio to Restore Database using BAK File 1

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

	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.

SQL Restore Database 5

Now you can see the Adventure Works 2017

DBs in Object Explorer 6

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.

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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.