Failed to restore a SQL Server 2008 database

Updated at:

When you restore a SQL Server 2008 database in SQL Server Management Studio, the process might fail with the error message: Cannot obtain exclusive access to the database because the database is in use. This topic describes the cause of and solution to this issue.

Problem description

When you restore a SQL Server 2008 database in SQL Server Management Studio, the process fails and the error message Cannot obtain exclusive access to the database because the database is in use appears.

Possible cause

The database is being used by another process.

Solutions

  1. In SQL Server Management Studio, locate and click the database that you want to restore in the navigation pane on the left.

  2. In the menu bar, click New Query (N).

  3. In the query window, enter the following command to find the process ID that is using the database and click Execute.

    use master
    select b.spid from sysdatabases a , sysprocesses b where a.dbid=b.dbid and a.name= 'database_name'

    For example, if the database name is aa, you can run the following command:

    use master
    select b.spid from sysdatabases a , sysprocesses b where a.dbid=b.dbid and a.name= 'aa'

    The following code block shows the query that returns the ID of the process using the database aa and the query result.

    use master
    select b.spid from sysdatabases a ,sysprocesses b where a.dbid=b.dbid and a.name='aa'

    The query result shows that the IDs (spid) of the processes that are using database aa are 55 and 58.

  4. In the query window, enter the following command and click Execute to stop the process that is using the database.

    kill <process_ID>
  5. Restore the database again.

    The database is restored.

    Note

    If the issue persists, submit a ticket.