SQL SERVER – Notes and Observations on ReadOnly Databases in SQL Server

In the past couple of weeks, I have written few blogs that revolve around utilizing “readonly” databases. It was fun working on this special case scenario that I had stumbled upon an interesting set of questions from my blog readers. These were not clearly called out in my previous blogs, hence thought of writing them down the responses I had given with examples in this blog. Some of the questions I have been asked were:

• Can I create temporary tables or global temporary tables when working with ReadOnly databases?
• Since the database is in ReadOnly mode, we cannot insert any values into the database. How about creation of tables when the DB is marked as ReadOnly?
• Can we create stored procedures when working with ReadOnly Databases?
• I have a large database and I see a lot of free space, can I shrink the DB when it is marked as ReadOnly? Is this allowed?

Some of these questions are interesting and require a small script to validate our understanding. A rule of thumb at this point is, this is a ReadOnly database and we need to know we cannot do anything with this database. Temp tables are created in the context of tempdb database and the readonly attribute doesn’t apply to objects created that way.

Next let me build the script to demystify each of the questions asked above:

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%)GOUSE MASTERGOALTER DATABASE [ReadOnlyDB] SET READ_ONLY
GO

The basic script above is creating our database and then setting the attribute to ReadOnly. Let us start our various tests to validate our understanding.

USE ReadOnlyDB
GO-- Creating our tableCREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))GO

As per our understanding, this must raise an error as shown below:

Msg 3906, Level 16, State 1, Line 15

Failed to update database “ReadOnlyDB” because the database is read-only.

Temp Tables: As discussed above, the below query in the ReadOnlyDB context willnot raise any error.

-- Creating our Temp TablesCREATE TABLE #t1 (i INT)GODROP TABLE #t1
GO-- Creating our Global Temp TablesCREATE TABLE ##t1 (i INT)GODROP TABLE ##t1
GO

Stored procedure creation: If we try to create any objects in our ReadOnlyDB, we will get the same error of 3906.

– Create the stored procedure inside ReadOnlyDB

-- Create the stored procedure inside ReadOnlyDBCREATE PROC prc1AS
BEGIN
SELECT 1ENDGO

Msg 3906, Level 16, State 1, Procedure prc1, Line 30

Failed to update database “ReadOnlyDB” because the database is read-only.

Shrink Database command: Shrink database question was an interesting one for me. But on second look, this is logical. Since shrink database will try to alter the header in our mdf file, in the readonly mode that is not allowed. Hence we will get the error.

DBCC SHRINKDATABASE (ReadOnlyDB, TRUNCATEONLY);GO

Msg 7992, Level 16, State 1, Line 4

Cannot shrink ‘read only’ database ‘ReadOnlyDB’.

All these errors are simple yet questions that come to many of us. This blog post is a dedication to those who took time in writing a line to me even after close to 7+ years of blogging. I get to learn from each one of you out there. SQL Server is an ocean and you make me complete.