SQL Server – How to set a database to read-only; Setting to Read/Write

Sometimes the need arises to migrate a databases for historical purposes and posterity to a lesser used data tier while still keeping the data accessible. Other times there is just the need for a read-only backup or sharable database for development purposes. Whatever the need, the requirement of read-only databases happens more often that one might think.

Usually the process involves conducting a full backup and restoration of one database to another SQL Server instance or on another environment.

Let’s start with the idea that you know how to export (backup) and import (restore) a SQL Server database. Then in order to make the database read-only or read/write we can use scripting seen in each respective section below.

Make a Database Read-Only

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

Make a Database Read/Write

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

Based on your need the scripts above should do the trick. Sometimes you get a error message stating the database is already in use. You can stop the database and restart it, then reapply the above selected script, or alternative you can update (ALTER) the database to run in single user mode which would use the following script logic:

ALTER DATABASE [TESTDB]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

In the case of single user mode, this query will rollback and transactions which may have been running on the database and puts the database into a single user mode configuration.