May 20, 2008 at 9:31 am
Hello,
Scenario: Two database servers. One production second read only replica. I do full backup and restore daily basis at night with standby option and then every 1 hour transaction log. The problem is that Transaction log fails when there are connections to read only database.
Who can provide me the best way to kill all connection and/or make database unavailable to be able start restore.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
May 20, 2008 at 10:09 am
If you search around you will find several scripts which will KILL all ser connections to a database. Personally I prefer setting the database in single user mode and rollback all active connections.
ALTER DATABASE MyDb SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Then start your restore from the same connection.
[font="Verdana"]Markus Bohse[/font]
May 20, 2008 at 1:29 pm
I'd do what MarkusB said, but if not, id recommend the following script as it can also send a NET SEND message to the user letting them know whats happening:
http://qa.sqlservercentral.com/scripts/Lock+and+Connection+Management/30030/
Matt.
May 21, 2008 at 1:26 am
Thanks guys. I like this first option but I remember I played a little with this scenario 1-2 years ago and after alter database further restores were not possible. I don't remmebr if I set this to Offline or Restricted.
Probably it was offline. Problem with restricted is that if user has rigths he can still connect. Is there any way to pause connections for particular database without affecting restore process?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply