Recently had to manage database deployments where the SQL Server was hosted with a cloud solution provider of repute. The database was quite huge and hence during backup and restore had to switch the same to Single User mode. However the database was so huge that even after service refresh the right click properties on the database was not able to open because of resource contingency.
Hence I chose to have this switch done through simple T-SQL which I would like to share the same here for everyone’s benefit.
To Switch to Single User Mode:
use master
go
alter database [YourDatabaseName] set single_user with rollback immediate
To Return Back to Normal/Multi-User Mode:
use master
go
alter database [YourDatabaseName] set single_user with rollback immediate
Additionally there is one more argument called WITH NOWAIT which could be used if needed
