Set a database to read only mode using SQL Server 2005 / 2008

Please visit my new Web Site WWW.Codedisplay.com

In most DBA or Database Administration purpose we need to set a Database to readonly mode. Here i will show you how you can set Database in Read Only mode for Sql server 2005 & Sql Server 2008. One thing keep in mind that you can not use same sql command for both sql server 2005 and 2008. Thats why here i will show the different ways to manage a Database to 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 2005:
Now run the below command:

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

After executing the above command then 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.



Now to remove the read-only mode run the below sql:

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

Sql Server 2008:
To make the Database read only in 2008 run the below SQL:

USE master;

GO

ALTER DATABASE databasename
SET READ_ONLY;

GO

Hope now you can perform DBA role.