Cursor Disconnecting Database Before Detach Operation in SQL Server
In this article, I will talk about the use of cursor that disconnects the database before detach in SQL Server.
Actually I wrote this Cursor on a question. Thinking it might be useful to someone.
While detaching in SQL Server, you may encounter errors upon connection. These errors can be solved with short written codes, or you can solve them using the Cursor I have written below.
DECLARE @dbname AS VARCHAR(80); DECLARE @server_name AS VARCHAR(20); SELECT @server_name = @@servername; DECLARE rs_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name IN ( N'TestIslem' ); --In this section, you can select as many databases as you want. OPEN rs_cursor; FETCH NEXT FROM rs_cursor INTO @dbname; IF @@FETCH_STATUS <> 0 PRINT 'There is no database to terminate the connection... Please check your script!!!'; WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'); EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS'); EXEC ('ALTER DATABASE ' + @dbname + ' SET MULTI_USER'); FETCH NEXT FROM rs_cursor INTO @dbname; PRINT 'Print ''SERVER NAME : ' + UPPER(@server_name) + '--> Successfully disconnected for all databases.'''; END; CLOSE rs_cursor; DEALLOCATE rs_cursor;
When you run the code, where I used a database called TestIslem. This will terminate any open connections on the database. In this way, you will successfully perform the detach process.
Good luck to everyone in business and life.