0

I am attempting to create a new database from a backup file of an old database that is still in use. At one point I was able to successfully restore the database, but had to delete it later after attempting to overwrite it with fresh data. Now when I try to restore the database I get an error that the database is in use. I am certain to specify a different name from the old and have even attempted to give it a different name than the one I was giving it before. I have also made certain to rename the data and log files to something other than the old database.

This is the stack trace for the error:

Restore of database 'Database' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.Perform(ITaskExecutionContext context)
   at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)

===================================

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.RestorePlan.Execute()
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)

We have tried restarting the SQL server to close any possible calls to the database, but this did not correct the issue. I have checked the 'overwrite' and 'close existing connections' boxes in the restore wizard, but these also have not corrected the issue. Any other tips on how to correct this issue?

5

You should offline the database and restore over it

Alter database db-name
Set offline with rollback immediate 
Restore database .. with recovery

Ditch SSMS GUI and use tsql.

| improve this answer | |
  • Since the database I was restoring didn't actually exist, setting it offline didn't do anything, but using tsql to restore it instead of the GUI worked. Thank you. – Justin Foley Jul 13 '16 at 13:53
  • @Justinfoley Exclusive access could not be obtained because the database is in use. .. it means that you were trying to restore on an existing database. – Kin Shah Jul 13 '16 at 13:55
  • I could not query against the database before. The first part of your SQL that set the database to offline gave this error: User does not have permission to alter database 'Database', the database does not exist, or the database is not in a state that allows access checks. I'm fairly confident it didn't exist – Justin Foley Jul 13 '16 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.