Change a database state in SQL — Parv The IT Geek

Have you ever wanted to change a databases state from read only to read write or vice versa? Well I am going to show you how using some easy to remember T-SQL which you will be using repeatedly over time.

We received a database from a client the other day and noticed one of my System Admin was struggling with the restore. He thought he was doing something wrong with the restore and I said he hadn’t done anything wrong and that the database was likely put into that state prior to being backed up and sent to us.

I showed him the below T-SQL which enabled him to change the state to read write and then change it to read only if required.

Make Database Read Only

GO

ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT

GO

Or

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY

GO

Make Database Read/Write

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT

GO

Or

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE

GO

---

Originally published at https://parvtheitgeek.com on February 12, 2014.