Set database to read only mode in SQL Server 2005 2008 2012

In most DBA or Database Administration purpose, we need to set a Database to read only mode. Here i will show you how you can set Database in Read Only mode for Sql server 2005, Sql Server 2008 and Sql Server 2012. One thing keep in mind that you can not use same sql command for all sql server versions. That’s why here i will show the different ways to manage a Database to make Read Only mode. The another important note is, you can not make a database read only until you set the Database in single user mode. So first set the database in single user mode. Click here to read how to set Database as single user mode.

Sql Server 2008 2012:
To make the Database Read Only in 2008 or 2012 run the below SQL:

USE master;
ALTER DATABASE yourdatabasename

SQL Server 2005:
Run the below command:

EXEC sp_dboption "YourDatabaseName", "read only", "True";

After executing the above command, refresh the database. You will see that the DataBase now set to Read Only mode like below:

Now if anyone try to enter or update a data into the database he will receive the below error:
Error Message: Failed to update database “DataBaseName” because the Databse is read-only.

Don’t forget to remove the read-only mode. Run the below sql command:

EXEC sp_dboption "YourDatabaseName", "read only", "False";

Hope you are going to be a DBA. Congrats :)

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *