Restore to the original database or Redirected Restore of a SQL database fails with the error "Final error: 0xe000037a - Backup Exec cannot overwrite the existing database to restore the SQL database." OR "Final error: 0xe0008492 - Database Query Failure."
Backup Exec cannot overwrite the existing database to restore the SQL database. OR An error occurred on a query to database TEST_DB. Exclusive access could not be obtained because the database is in use. Exclusive access could not be obtained because the database is in use
This issue can be resolved by redirecting the restore of the SQL database to a different server or to a database that does not currently exist on the same server.
However, if the data is to be restored over the existing database, perform the following steps:
1. Set the database to Single User Mode in the SQL.
- Open SQL Enterprise Manager or Object Explorer for SQL.
- Select the database whose restore has to be performed.
- Right click the database and select Properties.
- On the database's properties window, go to the Options tab and select Restrict Access to Single User Mode (Figure 1)
- Close SQL Enterprise Manager or Object Explorer
2. Verify that all SQL utilities such as Enterprise Manager, and Query Analyzer are closed, which can cause the error "Exclusive access could not be obtained because the database is in use".
3. Select the option Take existing destination database offline in Restore Job Properties to ensure that there are no active connections to the database during the restore.
Note: Even after setting the database to single user mode, it is possible that either SQL or some other third party application has an active connection to it, causing restore to fail.
4. Select Overwrite the existing database option in Restore Job Properties and run the restore. (Figure 2)
Note: The only database that is restored without taking it offline is the Master database. In most of the cases, a redirect is performed to a database that does not already exist and the missing tables are imported into the existing online database. Also, if a database needs to be completely restored to the state it was in when the backup ran, taking it offline is a better way to recover it, because if the database is too corrupted, an online restore may never be possible.