InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:36:14 GMT20http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxMake sure no other instances of SSMS are open.If you have multiple tabs open, make sure they are [u]not[/u] pointing to the DB in question.You could also try restarting the service.Any replication going on? Or any other jobs or triggers pointing to your troublesome DB?Thu, 27 Sep 2012 04:48:03 GMTDennis Posthttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxGood.Thu, 27 Sep 2012 00:30:52 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxIn my case: I was logged in to SSMS as 'user_x'. Default database for 'user_x' was 'Test_DB'.I got same errormessage when I tried to restore in this session 'Test_DB'.When I logged out as 'user_x' and logged in SSMS as another user (that did not have default database 'Test_DB'), restore was successful.Thu, 27 Sep 2012 00:28:13 GMTlembit.kristianhttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspx[quote][b]Grant Fritchey (4/5/2011)[/b][hr]True, but I prefer to not let anyone have privileged access to the server except other DBAs.[/quote]Of course, I would prefer that also - but I have various systems where the vendors have setup their applications to use privileged accounts. I don't have a choice...unfortunately.Tue, 05 Apr 2011 19:35:04 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxHi Sourav Mukherjee, You can use the below command.ALTER DATABASE [mydb] SET SINGLE_USER WITH ROLLBACK IMMEDIATEGORESTORE DATABASE [mydb] FROM DISK='f:\backup\mydb.bak'GOTue, 05 Apr 2011 15:08:17 GMTphaniverma.dbahttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspx[quote][b]Jeffrey Williams-493691 (4/5/2011)[/b][hr][quote][b]Grant Fritchey (4/5/2011)[/b][hr]Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.[/quote]If this doesn't work, then you have some process refreshing its connection and is using a privileged account.I prefer taking the database offline myself - this insures that nobody can access that database and allows the restore to proceed with no problems.[/quote]True, but I prefer to not let anyone have privileged access to the server except other DBAs.Tue, 05 Apr 2011 14:53:18 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspx[quote][b]Grant Fritchey (4/5/2011)[/b][hr]Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.[/quote]If this doesn't work, then you have some process refreshing its connection and is using a privileged account.I prefer taking the database offline myself - this insures that nobody can access that database and allows the restore to proceed with no problems.Tue, 05 Apr 2011 14:17:44 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxCan you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.Tue, 05 Apr 2011 05:17:54 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxmasterTue, 05 Apr 2011 03:16:18 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxWhat is the default database for the job step running the restore?Tue, 05 Apr 2011 03:12:56 GMTIan Scarletthttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxno, refresh of the database is being performed using a SQL JOB. I dont use the SSMS generally for this kind of tasks, it is prone to hang for large size bkps.Tue, 05 Apr 2011 03:05:40 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxAre you doing the restore from SSMS rather than a query? When SSMS opens its restore window, it starts a session using your default database. If this is the database you're trying to restore, you get the error described. (This has always annoyed me).Tue, 05 Apr 2011 02:16:07 GMTAndrew Watson-478275http://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxEven if you place it in single user mode, if you're connected to that database or another connection is there, it might still be active when you try to run the backup. Generally I don't use single user mode, I use restricted user, but that assumes that most of our logins are not 'sa' and the ones that are I can kill or identify and alert individually.Mon, 04 Apr 2011 06:33:06 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1087930-146-1.aspxHi,While trying to restore a database (with replace option), I am getting the below error -Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.Though, before kicking off the refresh, I took the database in 'single user' mode and also used the below script to KILL all the connected sessions.DECLARE @SPIDSTR varchar(8000)DECLARE @ConnKilled smallintDECLARE @DBName varchar(100)SET @SPIDSTR = ''SET @ConnKilled = 0;SET @DBName = '<database>'SELECT @SPIDSTR=coalesce(@spidstr,',' )+'KILL '+convert(varchar, spid)+ '; 'FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);Print @SPIDSTR;IF LEN(@spidstr) > 0 BEGINEXEC(@spidstr);Print @SPIDSTR; SELECT @ConnKilled = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) ENDCan you please suggest, what went wrong, in my adhered process?Atlast, I had to drop the database and now when I am trying to restore, it is working fine.Kindly suggest with your opinion.Mon, 04 Apr 2011 02:02:16 GMTSQL-DBA-01