Skip to main content

How to Restore a SQL Database?

  • Updated

Restoring a SQL Database

This page explains how you can restore a database. You'll find the steps below.
To download a PDF copy of this article, click here.

Steps

  1. Open Microsoft SQL Server Management Studio.

  2. Find the file at this location: Start\All Programs\Microsoft SQL Server 20xx\SQL Server Management Studio
    and click on it.

    SQL Server Management Studio Location

  3. Log into SQL Server with the SA account or windows account with sufficient privileges.

    Login SQL server

  4. Expand Databases by clicking on the + sign next to it. Your database should be listed below.

    databases in SQL server
  5. Right click on your database. 
    A fly out menu should appear. 
  6. Click on the listed item “Tasks” and select “Restore” from the options. 

  7. Select “Database” from the fly out menu.

    Fly Out menu

     

  8. The "Restore Database" Windows will open. On the General Page, click on the Device radio button and the box with the ellipsis to expand the “Select backup devices” dialog box.

    Restore Database Window

     

  9. Click the Add button on the Select backup devices dialog box.

    Select Backup Devices window

     

  10. The “Locate Backup File” dialog box will appear. Locate your backup file and click the “OK” button. 

    Locate Backup File window

     

  11. The backup file will be listed in the “Select backup devices” dialog box. Click the “OK” button.

    Select Backup Devices window 2

  12. The backup file will appear listed in the “Backup Sets to restore:” window. Click the Options page.

    Restore Database Window 2
  13. Click on the “Overwrite the existing database (WITH REPLACE)” checkbox.

  14. Click the “OK” button to restore your database.

    Overwrite Database


    If the restore is successful, the following prompt will appear: Database "name of database" restored successfully.
     

    Restore Successful Window
  15. Click the OK button.

 

 

Troubleshooting

“Error: Restore of Database ‘ACCESSCONTROL’ Failed”

Issue

  • Database restore fails when the database is currently in use by another program or service.

Resolution Overview

  • Stop all services connected to the database or

  • Restart the SQL Server and perform the restore before any connections are re-established.

Step-by-Step Resolution

  1. Begin the database restore process by following steps 1–15 outlined earlier, stopping just before clicking the “OK” button to execute the restore.
  2. Right click on the database server instance name.

    Instance name
  3. Select the “restart” option.
  4. The following prompt will appear. “Are you sure you want to restart the MSSQLSERVER service on (your computer name)?”
    Restart Server Window
  5. Click the “OK” button on the Microsoft SQL Server Management Studio dialog box.
    You will receive a progress bar indicating the progress of the restart.
    Restart of Database
    Once the restart has completed successfully, you will see the green arrow button next to the database server.
  6. Now, click the “OK” button to complete the restore. 
    This step must be completed quickly.
    If the application reconnects to the database before the restore is executed, the restore will fail again.

     

Related to

Related articles