Scenario:
When you attempt to Detach a SQL database, you may possibly end up encountering an error message as below:
C:\>SQLCMD.EXE -U sa -P P@ssword -S TESTDBSRV01\APPDB01 -i “C:\Detach-APP-DB.sql”
Msg 5064, Level 16, State 1, Server TESTDBSRV01\APPDB01, Line 1
Changes to the state or options of database ‘APPDB01’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server TESTDBSRV01\APPDB01, Line 1
ALTER DATABASE statement failed.
C:\>
Where in my C:\Detach-APP-DB.sql file contains below statement:
ALTER DATABASE HRINDDB01 SET OFFLINE WITH ROLLBACK IMMEDIATE
Solution:
To resolve this error, you need to ensure that no other users/applications/services/websites are connected to the Database that are holding database from being detached. In my case, I’ve a service that had connections to my database. I had to stop the service for the detach operation to proceed further.
