SQL Server - Set multiple database mode

Generate script for changing mode for multiple databases. In given script I’m setting databases to Single-user and back to normal stace. Then changing state to Read-Only and back to Read-Write, but you can use it for more database states.

It will generate T-SQL for you and you can run it then using Management Studio or by executing some script.

WARNING: Be aware of running generated script on production environment as it can make databases completely inaccessible

-- set all user DBs to read-only mode
SELECT 'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [' + name + '] SET MULTI_USER
GO
ALTER DATABASE [' + name + '] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [' + name + '] SET READ_WRITE
GO'
FROM sys.databases
WHERE database_id > 4

Results

Script messages after execution:

ALTER DATABASE [ReportServer] SET MULTI_USER
GO
ALTER DATABASE [ReportServer] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [ReportServer] SET READ_WRITE
GO
ALTER DATABASE [ReportServerTempDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [ReportServerTempDB] SET MULTI_USER
GO
ALTER DATABASE [ReportServerTempDB] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [ReportServerTempDB] SET READ_WRITE

Leave a Reply

Your email address will not be published. Required fields are marked *