The following error will be thrown...! When try to restore a database while the same being used/accessed.
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
The reason is very common as the Database being tried to restore is/are used/accessed somehere else through SSMS/Application(s)...
But, somebody says that, "First change the Database restrict access to SINGLE USER Mode and try to restore the same instead of KILLing the opened/accessed connections(SPIDs)"
Is that True ?
I don't think so!
Because, When you try to change the database restrict access to SINGLE USER mode using the below script/using the wizard. It'll close/KILL the opened/accessed sessions(SPID)
USE [Master]
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Because, "SQL Server will close all other connections to the database" before change the Restrict access mode to SINGLE USER mode!
So, Changing the Database restrict access mode to SINGLE_USER will also close(KILL) all the connection(s) to the database!
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
The reason is very common as the Database being tried to restore is/are used/accessed somehere else through SSMS/Application(s)...
But, somebody says that, "First change the Database restrict access to SINGLE USER Mode and try to restore the same instead of KILLing the opened/accessed connections(SPIDs)"
Is that True ?
I don't think so!
Because, When you try to change the database restrict access to SINGLE USER mode using the below script/using the wizard. It'll close/KILL the opened/accessed sessions(SPID)
USE [Master]
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Because, "SQL Server will close all other connections to the database" before change the Restrict access mode to SINGLE USER mode!
So, Changing the Database restrict access mode to SINGLE_USER will also close(KILL) all the connection(s) to the database!
I usually follow this method...so what we have to do other than that????
ReplyDeleteThe purpose of this article is, Somebody says like setting database restrict access to SINGLE MODE is the best/commonly used method instead of KILLing/Closing the Session/Connection!
ReplyDeleteI just wanted them to know that "Both methods are KILLs/Closes the Connection"!!!
I hope you agree with me!