Systems Engineering and RDBMS

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:

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.

One Response to “Making a database Read Only”

  1. […] to be in a read only mode and the scenarios when you would do that.  You can read more on that here.  A reader asked how to change it back to be a read/write DB.  It’s pretty simple and you […]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: