Making a database Read Only

We were in the process of migrating the data from a legacy system to a newly architected system for a client of ours.  For the duration of the migration, they needed the data set to be available for reporting purposes but of course no data should be created in that legacy system during the time of the migration.  So, one of the obvious choices were to make the database as a read only database.  In SQL Server, there is an option in the “ALTER DATABASE” command to achieve that.  In order to do so, one first has to mark the database in a single user mode first, then make the change to mark the database as a read only database and then change it back to the multi-user mode.

Here is a sample script:

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE DECIPHERTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Mark the database as Read Only*/
ALTER DATABASE DECIPHERTEST SET READ_ONLY
GO
/*Mark it back to Multi User now*/
ALTER DATABASE DECIPHERTEST SET MULTI_USER
GO

References:

• BOL entry for the ALTER DATABASE command – here.