Cannot restore SQL database backup to new database because new database is in use

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published.