How to set every database in a SQL Server instance to read only

The following TSQL when executed will create the TSQL needed to set all databases in a SQL Server instance to read only mode.

Typical use cases for this might include creating a copy of databases for reporting purposes or when migrating databases from one server to another.

SELECT 'ALTER DATABASE [' + NAME + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [' + NAME + '] SET READ_ONLY WITH NO_WAIT
ALTER DATABASE [' + NAME + '] SET MULTI_USER
GO'
FROM sys.databases
/*Setting database id > 4 excludes the system databases*/
WHERE database_id > 4

 

Before you run the outputted script you may need to kill open connections to the databases or certain running activities. Obviously you would want to make sure it is safe to do so before killing any activity.

To kill activities on mass you can use the script in this post.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s