October 28, 2014 at 10:03 am
Okay - I am trying to restore a 'TEST' database using a copy of my ' LIVE' (ERP Databases)
I am getting the can't restore due to exclusive access message.
I put the database into single user mode.
I then went to activity monitor and killed the one process that was connected to the database.
I then attempted restore (should have worked) - but I get the same message.
I go back to the activity monitor and surprised a new connection appears.
Obviously I have a service (we have a web server) somewhere that automatically connects - any suggestions on how to trace back the connection or how to kill the connection and block the connection?
Any guidance would be appreciated - I need to complete the restore so I can do some critical database testing.
October 28, 2014 at 11:21 am
Best thing to do is put the database into restricted user, not single user. Although, that presumes you're not letting your apps connect as 'sa'.
If you are, then have the script that sets the database into single user also be the script that kills the connections and then runs the restore, all as a unit.
----------------------------------------------------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
October 30, 2014 at 3:48 pm
Just run something like this:
USE [master]
GO
ALTER DATABASE [foo] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [foo] .....
GO
USE [master]
GO
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply