Knowledge Base

SQL error 3101 (database is in use) during full database restore

Category: Troubleshooting & error messages
Date: 15 Jul 2009
Product: SQL Backup
When performing a full restore over an existing database, SQL Backup may report that the database cannot be restored because it is currently in use and exclusive access could not be obtained.

In the situation where a full restore is blocked because users are currently connected to the database, the best solution to this problem is to take the database offline prior to running the restore, as this is the easiest way to kill all connections to the database. In a log shipping scenario, this is not desirable, as the log shipping would have to be re-initialized from the beginning. To work around this scenario in a log shipping job, please see here:

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase/SQL_Backup/KB200708000135.htm

To take the database offline, you may use the following SQL code fragment just before your restore job:

USE master
ALTER DATABASE [<db name here>] SET OFFLINE WITH ROLLBACK IMMEDIATE

SQL Backup versions 6 and higher implement an option to automatically disconnect all database users prior to the restore operation, so the above workaround would not be necessary.