A common task for a DBA is to restore a database to a new location. The script for the same is given below
USE [master] GO RESTORE DATABASE [AdventureWorks] FROM DISK = N'E:\Ahmad\AdventureWorks2014.bak' WITH FILE = 1, MOVE N'AdventureWorks2014_Data' TO N'E:\Ahmad\DATA\AdventureWorks_Data.mdf', MOVE N'AdventureWorks2014_Log' TO N'E:\Ahmad\Log\AdventureWorks_Log.ldf', NOUNLOAD, STATS = 10
The above command restores the Adventureworks2014 database to AdventureWorks database. The primary data file is created at e:\ahmad\data and log file is created at e:\ahmad\log folder.
In order to get the original database file location run below command
Restore filelistonly from DISK = N'E:\Ahmad\AdventureWorks2014.bak'