MSSQL Backup and Restore

Owned by Sanjay Sahani
Last updated: Aug 21, 2011Version comment

This section briefly describes how to backup and restore MSSQL database using the SQL Server Management Studio.

Backup

Taking backup using SQL Server Management Studio.

  1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.
  3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
  4. In the Database list box, verify the database name. You can optionally select a different database from the list.
  5. You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE). In the Backup type list box, select Full.
  6. For Backup component, click Database.
  7. Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
  8. Optionally, in the Description text box, enter a description of the backup set.
  9. Specify when the backup set will expire; a value of 0 days means that the backup set will never expire.
  10. Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.
    To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

Restore

Restoring using SQL Server Management Studio.

  1. After you connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases. Depending on the database, either select a user database or expand System Databases, and then select a system database.
  3. Right-click the database, point to Tasks, and then click Restore.
  4. Click Database, which opens the Restore Database dialog box.
  5. On the General page, the name of the restoring database appears in the To database list box. To create a new database, enter its name in the list box.
  6. In the To a point in time text box, either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box. For more information, see How to: Restore to a Point in Time (SQL Server Management Studio).
  7. To specify the source and location of the backup sets to restore, click one of the following options:
    1. From database
    2. Enter a database name in the list box.
    3. From device
      Click the browse button, which opens the Specify Backup dialog box. In the Backup media list box, select one of the listed device types. To select one or more devices for the Backup location list box, click Add.
      After you add the devices you want to the Backup location list box, click OK to return to the General page.
  8. In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Any backups that depend on a deselected backup are deselected automatically.