Sql-Server: Error - Exclusive Access Could Not Be Obtained Because the Database Is in Use

SQL-Server: Error - Exclusive access could not be obtained because the database is in use

I'll assume that if you're restoring a db, you don't care about any existing transactions on that db. Right? If so, this should work for you:

USE master
GO

ALTER DATABASE AdventureWorksDW
SET SINGLE_USER
--This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE AdventureWorksDW
FROM ...
...
GO

Now, one additional item to be aware. After you set the db into single user mode, someone else may attempt to connect to the db. If they succeed, you won't be able to proceed with your restore. It's a race! My suggestion is to run all three statements at once.

Exclusive access could not be obtained because the database is in use

A restore can only happen if the database does not have any connections to it (besides yours). The easy way on a MS SQL Server to kick all users off is:

ALTER DATABASE [MyDB] SET Single_User WITH Rollback Immediate
GO

Now, you can perform your restore with impunity. Make sure you set it back to Multi-user mode when you're done with the restore:

ALTER DATABASE [MyDB] SET Multi_User
GO

Backup/Restore from different database causing Restore failed exclusive access could not be obtained

A cause for the attempt to get exclusive access comes from the options page of the restore dialog in SQL Server 2012 Management Studio. It will turn on tail-log and leave in restoring state options for the SOURCE database. So, it will try to gain exclusive access to the source database (in this case A) in order to perform this action. If you turn off the tail log option, you will find that the operation works much more smoothly.

Unable to restore SQL database, exclusive access could not be obtained (single user mode)

If anybody is connected to your database, SQL Server cannot drop it, so you have to disconnect existing connections, as you have tried. The problem with single_user is, that it still allows a single user to connect. As you yourself cannot be connected to the database when dropping it you have to get out of there. That opens up that slot for someone else to connect and in turn prevent you from dropping it.

There are a few SQL Server processes that are particularly good at connecting to a database in that split second. Replication is one example. (You shouldn't really drop a database that is published anyway, bat that is another story.)

So what can we do about this? The only 100% safe way is to prevent users from connecting to the database. The only practical way is to switch the database offline and then drop it. However, that has the nasty side effect, that SQL Server does not delete the files of that database, so you have to do that manually.

Another option is to just be fast enough. In your example you bring the database back online before you drop it. That is a fairly resource intensive process that gives an "intruder" lots of time to connect.

The solution I have been using with success looks like this:

ALTER DATABASE MyDb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
USE MyDb;
ALTER DATABASE MyDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE tempdb;
DROP DATABASE MyDb;

This first sets the database to restricted user and connects to it. Then, while still connected it sets the database to single user. Afterwards the context is switched to tempdb and the drop is executed immediately thereafter. Important here is, to send these commands as one batch to SQL Server to minimize the time between the USE tempdb; and the DROP. Setting the database to restricted user in the beginning catches some rare edge cases, so leave it in even though it does not make sense at first glance.

While this still leaves a theoretical gap for someone else to get in, I have never seen it fail.

After the database is dropped you can run your restore as normal.

Good luck.

Restore DB using SMO without DATABASE is in use error

I resolved the issue by killing the connected processes and database

ServerConnection con = new ServerConnection(_serverName, _userName, _password);
Server server = new Server(con);
Restore destination = new Restore();
destination.Action = RestoreActionType.Database;
destination.Database = _databaseName;
BackupDeviceItem deviceItem = new BackupDeviceItem(path, DeviceType.File);
destination.Devices.Add(deviceItem);
destination.ReplaceDatabase = true;
destination.NoRecovery = true;
server.KillAllProcesses("your db name");
server.KillDatabase("your db name");
destination.SqlRestore(server);

---

Related Topics