Making a database Read Only
Posted by decipherinfosys on June 20, 2009
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:
/*Mark it as Singe User*/
ALTER DATABASE DECIPHERTEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/*Mark the database as Read Only*/
ALTER DATABASE DECIPHERTEST SET READ_ONLY
/*Mark it back to Multi User now*/
ALTER DATABASE DECIPHERTEST SET MULTI_USER
- BOL entry for the ALTER DATABASE command – here.