Restore SQL Database using SQL Script

Copy the .bak file to the physical drive (i.e. D:\ or E:\ etc.) of the machine.
Open Query Analyzer from Start Menu or from Enterprise Manager of SQL for the concerned server. Once inside log into the Query Analyzer.

Type
DATABASE FIILELISTONLY FROM DISK = 'F:\ABC01122008.bak' in order to get the LogicalName for 'ABC_log' and 'ABC_data'.


Apply the following script to restore SQL database using SQL Script & .BAK file.
RESTORE DATABASE {db name} FROM DISK =‘C:\{db name} .bak’ –Backup file path WITH MOVE ‘Logical_Name_Data’ TO D:\SQLDATA\MSSQL\data\{db name}_Data.MDF’, –MDF file destination path MOVE ‘Logical_Data_Log’ TO ‘D:\SQLDATA\MSSQL\data\{db name}_Log.LDF’, — LDF Location STATS = 1, REPLACE GO

Make sure to replace the {db name} = Name of your Database and Logical_Data = Name of the Logical file — File system name.

Here is complate code of the same
RESTORE DATABASE CTS2
FROM DISK = 'F:\CTS.bak' WITH REPLACE,
MOVE 'ABC_data' TO 'E:\SQLDatabase\CTS2_data.MDF',
MOVE 'ABC_log' TO 'E:\SQLDatabase\CTS2_log.LDF', STATS = 1, REPLACE
GO


Above script snippet will replace the existing database at the specified location. You can also rename the .MDF and .LDF files as you like.