How to: Backup and restore a SQL Server 2008 database through the SQL Management Studio

SQL Management Studio offers an easy and straightforward way to back-up databases and restore them to another server. Just follow the steps:

To back up.

1. Connect to the server
2. Select the database
3. Right click and select “Back up…” from the menu “Tasks”.
4. In the new box, (by default, it will open in the General page) select the backup type “Full”, component will be the Database, you can type any name you wish in the backup set section (better leave the default), select a date or number of days for the expiry date (or, 0 for never) and, finally, click on Add to select the folder where the backup will be created. If you’re happy with the provided one, you don’t have to add a new one. If you do add a new one, you may want to remove the existing one.
5. In the Options page (you change these in the “Select a page” section on the left hand side), you may want to select options for the backup media. I normally leave these options as is. Be certain of what you’re doing if you select “Overwrite all existing backup sets” or, if you backup to a new media set and erase all existing backup sets.
6. Click OK. The back up file will be created in the folder you selected.

To Restore.
1. connect to the new server
2. create a new database (select the folder where you want it to be stored)
3. right click on the new database and select Restore from the menu “Tasks” and then “Database”
4. in the new box, (by default, it will open in the General page) select “From device” in the Source for Restore section. Click on the ellipsis, then add, and select the backup file you created earlier
5. the file you selected will appear in the “Select the backup sets to restore” section after you click on OK. Remember that you will have to tick the box next to the backup file, otherwise the studio will complain.
6. you will now need to open up the options page (on the left hand side) and check the box “overwrite the existing database (WITH REPLACE)”.
7. Also, you will need to change the file locations in the “Restore the database files as” section. You will have to select the files that were created when you first created the database in step 2.
8. Click OK

UPDATE: If at the time of restore you receive this error “The media set has 2 media families but only 1 are provided. All members must be provided” it probably means that when you were doing the backup, you specified more than one files in the  Destination box. Notice how there already exists a file in the Destination box when you try to backup. You need to remove the existing file (log), and define a new destination file for the backup. This way, all the information for the database will be backup up in one file that you can later use to restore some place else.