SQL Server – Can’t Restore Database Using SSMS GUI

sql serverssms

I have two databases. One is production (A), the other is development (B).

I use to be able to restore database from a backup.BAK file of A in B by using SSMS > right clicking databases > restore database and using the appropriate settings.

Today I tried the following and in every situation I got the same error "Exclusive access could not be obtained because the database is in use"

  • Took database B offline (and selected option to drop all connections)
  • Tried with database B online and choosing option to drop active connections with risk of being left in single user mode (got stuck in single user mode and restore still failed with same error).
  • Tried with database B online and without choosing option to drop active connections.
  • Tried with database B online and with option to overwrite the existing database and without and with and without option to close existing connections to destination database.

I'm using SSMS 2017 and just looking for a simple way to restore database B with a copy of A. Anybody know a way to resolve?

Best Answer

I would try:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH NO_WAIT

Note this will make the database inaccessible but guess that doesn't matter if you are going to restore over the top of it anyway.

If that fails then:

USE master;
GO
ALTER DATABASE B SET OFFLINE WITH ROLLBACK IMMEDIATE

But if a big transaction is in progress then it could take a long time to rollback.

Related Question