SQL Restore Database

In this section, we will explain to you the step by step approach for SQL restore database procedure using the BAK file. For this Restore Database in SQL Server 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 Database. The SQL server does not have the AdventureWorks2017 database

TIP: Please refer Backup for creating a backup, and Maintenance Plan for creating 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

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

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 is executed, 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'