Backup and Restore SQL Server Databases Using SQL Server Management Studio

Backing up SQL Server database is a good precautionary move against SQL server disasters that can happen due to various reasons such as media failures, user mistakes, hardware damages etc. This is a widely used SQL Server Recovery method. It is possible to backup and restore SQL Server databases using the SQL Server Management Studio. Follow the steps given below to backup SQL Server database. Here we assume that you are using SQL Server 2012.
Full Database Backup – Using SQL Server Management Studio

1. In the Object Explorer, expand databases, select a user database (or expand system databases and select a system database).
   
2. Right-click the database, click Tasks > Backup.
   
3. Enter details (as described below) when the Databases dialogue box appears (by default General in the Select a page pane is selected).
   

Source Section

• Check the Database list box and verify the database name (change the database name if required).
  
• Enter the recovery model (FULL, BULK_LOGGED, or SIMPLE) in the Recovery model text box.
  
• Select the backup type as Full in the Backup type list box.
  

Note - With the Full option selected above, you can optionally create copy-only back up by checking the checkbox Copy Only Backup.

• Choose Database as the Backup component.
  

Backup set Section

• Enter a Name for the backup set (you can use the default name suggested the software).
  
• Enter description in the Description text box (this step is optional).
  
• Enter the backup set expiry information: choose After / On and enter the required values.
  

Destination Section

• Choose Disk or Tape as the destination, and click Add; use the Select Backup Destination dialogue box to select the required destination. The added destination appears in the Back up to list box.
  
  

4. Click Options in the Select a page pane on the left of the dialogue box. This page shows the advanced options. Enter the details (as described below) as required.
   
   

Overwrite media Section

• Select an Overwrite media option:
  
• Backup to the existing media set
  

After selecting this option, select Append to the existing backup set OR Overwrite all existing backup sets.
Note: - Check media set name and backup set expiration is an additional option. It verifies the expiry information of the backup set. You can optionally provide the media set name in the Media set name text box.

• Back up to a new media set, and erase all existing backup sets.
  

After selecting this option, enter the media set name in the New media set name text box (optionally, you can add a description in the New media set description text box).
Reliability Section
Select the required options from the three:

• Verify backup when finished
  
• Perform checksum before writing to media
  
• Continue on checksum error
  

Tape drive section (This section is enabled only if the destination selected is Tape drive)
It has two options:

• Unload the tape after backup
  
• Rewind the tape before unloading
  

Compression section

• SQL Server 2012 supports backup compression. You can select the required options in the Set backup compression list box. The options are shown below.
  
  

5. Now, click General in the Select a page pane on the left of the dialogue box. Click OK.
   

6. Click OK when a message appears after the completions of the database backup.
   

Restore SQL Server Databases Using SQL Server Management Studio

Backing up SQL databases is a routine process in organizations, owing to the criticality of data carried by them. After events of data losses, users can restore the lost data from the backups. Users can use SQL Server Management Studio for restoring a full backup. The steps for SQL Server Recovery are given below (for SQL Server 2012):

7. In the Object Explorer, expand databases, select a user database (or expand system databases and select a system database).
   
8. Right-click the database, click Tasks > Restore > Database.
   
9. Enter the details (as described below) when the Restore Databases dialogue box appears (by default General in the Select a page pane is selected); click OK.
   
   

Source Section

• Select the source database from the Database list box.
  
• Click the browse button (…). Enter the details when the Select backup devices dialogue box appears. Select device type from the list in the Backup media type box (you can use Add to add more devices); click OK.
  
• Select the database to be restored in the Database list box (it is available only after the selection of Device in the previous step).
  

Destination Section

• Verity the name of the database to be restored in the Database box (you can change the name if required).
  

Timeline Button

• Click the Timeline button.
  
• Enter the details (as described below) when the Backup Timeline dialogue box appears.
  
  

• Click Specific date and time. Enter the time and date using the Date and Time boxes (you can also use the slider bar for it); click OK.
  

Restore plan Section

• Backup set to restore grid now displays the backups (that are necessary to perform the restore action).
  

10. Click Options in the Select a page pane on the left of the dialogue box. The Options page shows the advanced options. Enter the details (as described below) and click OK.
    
    

Restore options Section
Select appropriate option from the three:

• Overwrite the existing database (WITH REPLACE)
  
• Preserve the replication settings (WITH KEEP_REPLICATION)
  
• Restrict access to the restored database (WITH RESTRICTED_USER)
  

Select an option in the Recovery state box from the three:

• RESTORE WITH RECOVERY
  
• RESTORE WITH NORECOVERY
  
• RESTORE WITH STANDBY
  

Tail-Log backup Section
There is no need to modify the settings in this section. However, you can
- Check the checkboxes:

• Take tail-log backup before restore
  
• Leave source database in the restoring state
  

- Select the Backup file path
Server connections Section

• Check the checkbox Close existing connections to the database.
  

Prompt Section

• Optionally, you can check the checkbox Prompt before restoring each backup.

11. Click OK when the confirmation message appears after restoring the database.

Visit this link http://downloads.softwaredatarecovery.net/download-sql.php to download Kernel for SQL Recovery Tool