2 Workarounds to Update a Read-only Database in SQL Server

If a database is read-only, you can easily identify the status from the sys.databases or from the SSMS. A well-known fact is that you cannot update data in a read-only database. In this article, we will learn how to troubleshoot and fix this.

Two options

We can either use SQL Server Management Studio i.e., SSMS or T-SQL script to convert a read-only database into a read-write database. To understand these options better, let us

1. Create a database
2. Create tables within it
3. Add records to the table
4. Make the database as read-only
5. Try adding records to the same table
6. Review the error message
7. Convert the database back to read-write mode

Prepare Database

From your SQL Server management studio, connect to your SQL server, right-click the node “Database” and then select the option “New Database”. Press Ok after typing in a name for the database. You can also use the T-SQL script as shown in the example below:

CREATE DATABASE [DataNumen]
 CONTAINMENT = NONE
 ON PRIMARY 
( NAME = N'DataNumen', FILENAME = N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataNumen.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DataNumen_log', FILENAME = N'E:\Program Files\MSSQL12.MSSQLSERVER\MSSQL\DATA\DataNumen_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Create a table from SSMS by selecting the database, right click on the node “Tables” and then point to “New” and click the option “Table…”. Enter Column name and datatypes and then save with a table name. You can use the query below to create a table:

USE [DataNumen]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
 [Col1] [nchar](10) NULL
) ON [PRIMARY]
GO

Add records to the table using the following query:

Insert into [dbo].[Table1] values ('Testvalue')

The result window of SSMS will show the following message:
 
(1 row(s) affected)

Using SQL Server Management Studio, right-click the database and then select the option “Properties”. On the Database properties page, under “Options”, set the option “Database Read-Only” from “True” to “False”. You can also switch the database to read-only using TSQL script as shown below:

USE [master]
GO
ALTER DATABASE [DataNumen] SET READ_ONLY WITH NO_WAIT
GO

You could see that the SSMS now would show the status “Read-only” near the database name.

Now, let us try to insert a new record into the table.

Insert into [dbo].[Table1] values ('Testvalue2')

The following error message will appear in the result window.

Msg 3906, Level 16, State 1, Line 12
Failed to update database "DataNumen" because the database is read-only.

Revert the database status to read-write using Database Properties page and set the option from “Database Read-Only” from “False” to “True” or execute the following script:

USE [master]
GO
ALTER DATABASE [DataNumen] SET READ_WRITE WITH NO_WAIT
GO

Now, when you try to add a record, the error message “Failed to update database “DataNumen” because the database is read-only” will disappear. We all know that database backups help us to fix SQL Server databases. Howevere, a backup of read-only database will restore a database in read-only mode and not in read-write mode.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook recovery and excel recovery software products. For more information visit www.datanumen.com