In this step by step SQL Tutorial I will show you how to using SQL Server 2012 restore database from backup file. For the purpose of this tutorial I will use SQL Server 2012 but the steps should be fairly similar in SQL Server 2008 and 2008 R2.
SQL Server 2012 Editions: This tutorial is created using SQL Server 2012 Business Intelligence Edition but the steps should be the same in Enterprise edition and although we haven't tried it they should be the same (maybe with fewer options) in Express edition and standard edition of SQL Server 2012.
SQL Server Versions: Please be aware that you can restore 2012 backup files and you should be able to restore previous versions (althought haven't tried yet) but what you cannot do is to use previous version of SQL for instance SQL Server 2008 and try to restore backup file that was created using SQL Server 2012.
SQL Server 2012 Restore Backup file
Open object explorer in the toolbar. Click connect icon. You will be presented with "connect to server" pop up. Ensure Server Type drop down box has "Database Engine" selected. Type server name whichin my case is localhost and I use shortcut dot "." Click Connect button.
Now we can go to Databases folder and right click on it, from drop down box select Restore Database.
Restore Database window should appear. I am in General section, In Source section I selected Device radio button and clicked on the icon with three dots.
In the Select backup devices, click Add button
and now select Backup file location and file actual backup file. By default it should point to the correct path in most cases.
NOTE: If you cannot see the .bak files it might be because they don't have .bak extension on your file. In such situations change bottom right drop down box from backup files to all files.
After you select the file; the file name appears in the File name text box at the bottom. Click OK
Once we selected backup file we can see the path in the Backup media section. Click OK
In SQL Server 2012 after you click ok and certain fields are pre-populated. Please be aware that this new behaviour and you will most likely not see in previous version for example SQL Server 2008.
Source // Database drop down box shows available databases in the backup file.
Destination // Database drop down box shows database available in the server that you are connected to. You can type any database name and it will create it.
NOTE: When you click Database drop down box in destination section. You will notice that the database name will automatically change and you won't be able to change it to original value using drop down box but you can still type it. Hopefully Microsoft will change this behaviour as it is not very intuitive.
Backup sets to restore section contains all available backups. We have only full but sometimes you can have different backup types with multiple lines (we will try to write article and post link here. Remember to use search bar that will check hundreds of our articles).
KNOWN ISSUES: If you don't see OK button; it might be because your screen resolution it too low. This issue seems to be more frequent with virtual machines that are usually on lower screen resolutions. To fix it try to increase the screen resolution.
After we click Ok we get message box which in our case says "Database 'database name' restored successfully'"
NOTE: When you perform the database restore and the database already exists than it cannot be in use, otherwise restore operation may fail.
NOTE: You should see restored database under databases folder however if you use previous version of SQL Server you might have to right click databases folder and click refresh.
I hope this step by step tutorial will help you with your sql server 2012 database restore.