How to Detach SQL Database if DB is in Single User Mode

Hi Readers,

I came across a scenario where I need to detach the SQL Database and then take backup of .ldf and .mdf files.
Copy is not possible unless we do not detach the DB. This single user mode is set by SQL Server inorder to prevent corruption of database tables.

You can set the database back to online mode or say multi user mode by executing following commands.

exec sp_dboption 'your db name', 'single user', 'FALSE'

If error still persists, then you need to check which your is having connection with the database and find the particular spid

Inorder to find the connected users execute following command.

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid  where d.name = 'your db name'

This will fetch the users with their $spid. Kill the spids you see using following command.

Kill <spid>  : Here <spid> will be some numeric id like 125, so write Kill 125

again run the 1st command and you will be back to Multi User Mode.