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 file.
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
As you can see, the MDF, and LDF file 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 file to restore a database in SQL Server.
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 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'
The query is executed, and the 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 use the Restore File System to retrieve the Logical Filenames from the database backup.
RESTORE FILELISTONLY FROM DISK = 'D:\Backups\AdventureWorks2017.bak'