Sunday, 21 January 2018

How to Set a Database to Read-Only in SQL Server

I was having a discussion with one of my colleague regarding the database setting to Read-Only mode. This happened to Read-Only as part of the industry requirement for the databases, since there shouldn’t be any change in the data for the respective database.

Let’s check on how to set a database to Read-Only mode in SQL Server.

Below is the T-SQL script to change a database to Read-Only mode: 

USE [master]
GO
ALTER DATABASE [Arena3] SET READ_ONLY WITH NO_WAIT
GO



Below is the T-SQL script to change a database to Read/Write Mode:

USE [master]
GO
ALTER DATABASE [Arena3] SET READ_WRITE WITH NO_WAIT
GO

In case you would like to rewind the changed database modes from Read-Only access then simply use the command as:

ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note:  Please try not to use these commands on your production databases, which can probably have adverse effects for scheduled jobs and client update requests. 

0 comments: