Have you ever wondered if it is possible to encounter a ReadOnly error in SQL Server without actually making the database ReadOnly? Well, in this blog post, we will explore a special case scenario using Snapshot databases that will help us understand this behavior.

Let’s start by creating a database called ReadOnlyDB that we will use for testing:

CREATE DATABASE [ReadOnlyDB]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10%)
GO
USE ReadOnlyDB
GO
-- Creating our table
CREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))
GO

Now, let’s create a snapshot database out of our ReadOnlyDB:

-- Create a snapshot on our DB
CREATE DATABASE ReadOnlyDB_SS ON
( NAME = ReadOnlyDB, FILENAME =
'C:\Temp\ReadOnlyDB_data_1800.ss' )
AS SNAPSHOT OF ReadOnlyDB;
GO

It is important to note that snapshot databases are created as ReadOnly databases. This means that any attempt to modify data in the snapshot database will result in a ReadOnly error. Let’s test this by trying to insert some data into our Snapshot DB:

USE ReadOnlyDB_SS
GO
INSERT INTO tbl_SQLAuth VALUES (1, 'SQLAuth');
GO

As expected, we receive the familiar error message (3906): “Failed to update database ‘ReadOnlyDB_SS’ because the database is read-only.” Even though we have not explicitly marked the snapshot database as ReadOnly, we are still getting this error.

Now, let’s see what happens if we try to mark the snapshot database as ReadOnly again:

-- Let us set the Snapshot DB as READ_ONLY
USE MASTER
GO
ALTER DATABASE [ReadOnlyDB_SS] SET READ_ONLY
GO

Here, we encounter the error message (5093): “The operation cannot be performed on a database snapshot. ALTER DATABASE statement failed.” This error indicates that we cannot mark a snapshot database as ReadOnly.

Similarly, if we try to make the snapshot database Read_Write enabled, we will encounter the same error:

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

Again, we receive the error message (5093): “The operation cannot be performed on a database snapshot. ALTER DATABASE statement failed.” This confirms that we cannot mark a snapshot database as Read_Write enabled.

With these learnings in place, it is important to remember to clean up after testing. Make sure to drop the Snapshot database before deleting the main database. Here is the cleanup script for this scenario:

-- Clean up time
USE MASTER
GO
DROP DATABASE ReadOnlyDB_SS
GO
DROP DATABASE ReadOnlyDB
GO

By exploring the behavior of Snapshot databases in SQL Server, we have gained insights into the limitations of marking a snapshot database as ReadOnly or Read_Write enabled. Understanding these concepts can help us make informed decisions when working with SQL Server databases.