Generate script for mass setting database in read only mode

The following script will help you to generate a script which you can use for setting all databases (or a certain list) in READ_ONLY mode. Yes, this is quite a trivial task, however directly setting the database read only does not work, as there are user connections, which are blocking the operation. So I am using the following logic – set the database to single user, make it read only and then alter it once again for multi user access. Just have in mind that when using the script, just get the Result as text (Ctrl+T). You should also be careful with the distribution(s) databases you have on the instance, so you do not set them in read only state.

select 
'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [' + name + '] SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE [' + name + '] SET MULTI_USER
GO'
from sys.databases
where database_id > 4

3 Replies to “Generate script for mass setting database in read only mode”

  1. Excellent! Works great. Just need to add line breaks to the resulting script before and after each GO statement. A global wildcard search & replace takes care of that. Thanks for sharing!

  2. Simple but very useful, i have 29 servers with multiple DB’s on each that i need to set read_only just saved me lots of time thanks!!!

  3. Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘GO’.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘GO’.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.