July 23, 2009 at 2:02 pm
Cannot get exclusive access to database for restore. I looked in th activity monitor and there are .net sqlclient data provider connections. If I kill the process, it restarts again. What service do I turn off or what process should I run to disable .net sqlclient data provider so that I can restore the database.?
July 23, 2009 at 2:10 pm
Just take the database offline before starting the restore:
use master
alter database MyDatabase set offline with rollback immediate
July 23, 2009 at 2:20 pm
Is this the command to bring it back online after the restore:
use master
alter database MyDatabase set online
July 24, 2009 at 5:26 am
You could also set the database to restricted user and use rollback immediate, assuming the connection that keeps getting in is not an admin connection.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 24, 2009 at 7:52 am
After posting I found that the application server which has a service running on it to connect to the database server is what is initiating the .net sqlclient connections. Since I don't have access to the application servers, I will try both of the suggestions above to access. Thanks you guys for you time and help.
July 24, 2009 at 8:05 am
Patricia Johnson (7/24/2009)
After posting I found that the application server which has a service running on it to connect to the database server is what is initiating the .net sqlclient connections. Since I don't have access to the application servers, I will try both of the suggestions above to access. Thanks you guys for you time and help.
If you mean to say that after u put the db in single user mode, the application is still able to connect(as there is one available connection), i suggest u disable the TCP/IP and other protocols from sql server configuaration manager.
July 24, 2009 at 8:15 am
No, I don't mean that. What I am saying is that I will put the database in offline mode and then kill the .net sqlclient connections to the database so they cannot reconnect and the restore the database. Once I am done, I will put the database in online mode and the .net sqlclient connections should re-establish. That is what I am hoping for. I will find out here shortly.
July 24, 2009 at 8:23 am
Patricia Johnson (7/24/2009)
No, I don't mean that. What I am saying is that I will put the database in offline mode and then kill the .net sqlclient connections to the database so they cannot reconnect and the restore the database. Once I am done, I will put the database in online mode and the .net sqlclient connections should re-establish. That is what I am hoping for. I will find out here shortly.
This is other way round. THe moment you put the database offline, all your .net connections will be automatically terminated. you dont hv to do it. Michael's code above would be just fine to put the db in offline mode. You dont have to worry abt killing the sessions. 🙂
July 24, 2009 at 8:26 am
Oh cool! That's even better.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply