Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,377,803 Views

Archive for June 20th, 2009

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.

Posted in SQL Server | 1 Comment »