I need to perform a restore of database from .NET using SMO.Prior to the restore I set the database into RESTRICTED_USER WITH ROLLBACK IMMEDIATE mode. This works fine during testing/debugging - all users are dropped. However, when this code is run as part of an automated process at night, sometimes I get the following exception:
Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally
What's happening here? Thanks!
Can you not do;
alter database <db_name> set single_user with rollback immediate
Then add another TSQL step after the backup:
alter database <db_name> set multi_user
Alternatively, to find out what could be happening as this is running at night, somehow capture the results of
EXEC sp_who2
Which would show you who is connected at the time.