Restore a Database from the SQL Command

Assume your Database "SampleDB" has full backup SampleDB.bak. It can be restored using following steps. Run this queries in the maste database.

1) Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'E:SampleDBBackupfile.bak'

2) Use the values in the LogicalName Column in following Step.

-- Make Database to single user Mode
ALTER DATABASE SampleDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

-- Restore Database
RESTORE DATABASE SampleDB
FROM DISK = 'E:SampleDBBackupfile.bak'
WITH MOVE 'MDFLogicalName' TO 'D:SampleDBDataFile.mdf',
MOVE 'LDFLogicalName' TO 'D:SampleDBLDFFile.ldf',
REPLCE

/* Please run below statement if any error occures in the above Statement */
-- Convert Database again to Multi User Mode
ALTER DATABASE SampleDB SET MULTI_USER

In the above RESTORE Statement 'MDFLogicalName' and 'LDFLogicalName' are the logical names of the backup file which can be retrived from Step 1.

'D:SampleDBDataFile.mdf' and 'D:SampleDBLDFFile.ldf' are the physical File paths for the SampleDB.

Reference : blog.sqlauthority.com

Gopikrishna