Settings your SQL Server database to read-only mode (and back)

At times, for example in cases of emergency, being able to put a production database into read only mode can be very useful. When running into trouble with a production database, you usually don't have much time to start searching around the web, so it's better to prepare beforehand.

Here are two SQL statements that allow you to put a database into read-only mode and back to regular read-write mode. As you might guess, you will need to be an administrator to be able to run these commands. Additionally, you must have exclusive access to the database.

To put your database into read-only mode, run the ALTER DATABASE statement as follows:

ALTER DATABASE MyDatabaseName
SET READ_ONLY

And to get back to the normal mode, run:

ALTER DATABASE MyDatabaseName
SET READ_WRITE

In case the exclusive mode cannot be obtained (such as, there are too many users currently), you can try:

ALTER DATABASE MyDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

To get back from the single-user mode, set the option "MULTI_USER" to resume normal options:

ALTER DATABASE MyDatabaseName
SET MULTI_USER;  

Hope this helps!