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.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: