SQL Restore Database

In this article we will show you, the step by step approach for SQL restore database procedure using BAK file. For this demonstration, we are going to use the adventure works 2017 .bak file.

TIP: Please refer Backup Database for creating a database backup, and refer Maintenance Plan for creating regular database backups.

SQL Restore Database using BAK File

From the below screenshot, you can see that the SQL server does not have the AdventureWorks2017 database

As you can see, the Mdf, and ldf file corresponding to the Adventure Works 2017 are not there in C Drive

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

Syntax to restore Database from 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 database.

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

From the below screenshot you can see that the query is executed, and database is restored successfully.

Now you can see the Adventure Works 2017 database

The above process has automatically added the Mdf, and ldf files to the C Drive

If you don’t know the logical file names then you can use the Restore File System to retrieve the Logical File names from the database backup.

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