Here are detailed instructions on how to restore a database on SQL Server 2005 / 2008.

Before you begin, you must have a backup copy of the database you wish to restore.  The backup file had to be created with SQL Server  (thus, not a .mdf file).  If the backup file is compressed, it must first be extracted.
The backup file must be copied to the hard drive before starting.  SQL Server won’t allow you to restore from a network drive, flash drive or CD.

•  First you need to open SQL Server Management Studio
  Click on the Start Button, then click on Programs / All Programs, then click on Microsoft SQL Server 2005 and then click on SQL Server Management Studio.
  Log into SQL Server using Windows Authentication (your user account will have to be added to the system administrator's role in SQL Server)
• Go to the list of all the databases stored on the server
  On the left, expand the tree until you reach the Databases node.  The tree should look something like this:
  • (computer name) -> Databases
• Now you must determine where to restore the database to  (physical location of data files on hard drive)
  
  • If you are replacing an existing database on the server, you must first locate the current data file locations.
    You can do this by right-clicking on the database you wish to overwrite and click on the Properties menu item.  Click on the Files page on the left.  In the list, there should two items.  The data / rows file and the log file.  Scroll to the right to see the Path and File name columns.  Write down the path and file name for both items.  This is where the data file is stored.
    Click Cancel to close the Properties dialog box.
  • If the database doesn’t already exist on the server, you should either locate a path to restore the database to or create a new one.
    For HR Manage, we usually create a folder in the root called HRManage and store all application related files there.  Under the HRManage folder, we create a Data and Backup folder.  One for storing the data files and one for storing database backups.  Your filenames will then be something like C:\HRManage\Data\HRManage_data.mdf for the data file and C:\HRManage\Data\HRManage_log.ldf for the transaction log.  Whatever you choose your filenames to be, write down the full path with the filename for use in the next step.
• Right-click on the Databases node, click on the Tasks menu item and then click on Restore and then click on Database…
  
  • To database:  If you are replacing an existing database, choose the database you are replacing from the dropdown list.  If you are restoring a new database, type the name of the database in the field.  (e.g. HRManage, HR_PeopleRes, etc.)
  • Source for restore:  Click on the From Device option.  Then click on the ... button, then File should be selected in the dropdown box and click on the Add button and browse for the SQL Server backup file that you copied to the hard drive.  Then click Ok until you’re back at the main Restore Database dialog box.
  • There is now an item in the list of backups.  Mark the checkbox next to the database you wish to restore.  (There will usually be just one)
  • Now click on the Options page on the left.
  • Select the Over the existing database checkbox
  • In the list, type the full location of the data file and transaction log in the Restore As column as you wrote it down in the previous step.  You will have to join the Path and File name by separating it with a \.  E.g. Path = "C:\HRManage\Data", Filename = "HR_PeopleRes.mdf", type = "C:\HRManage\Data\HR_PeopleRes.mdf"
• Click Ok to restore the database