#0326 – SQL Server – Setting database to READ_ONLY does not change the file-group properties


Last week, I re-learnt something which, according to me, is a little counter-intuitive. I was studying read-only databases out of academic interest and noticed something interesting:

When a database is marked as Read-only, the underlying file/file-groups are not marked Read-only.

Here’s a simple test wherein we:

  1. Create a test database with multiple file-groups (the test works equally well with a single file-group)
  2. Set the database to READ_ONLY
  3. Check the file-group properties
USE master;
GO
--Create the test database
CREATE DATABASE ReadOnlyDB
ON
PRIMARY  (Name=ReadOnlyDB_Primary,
          FileName='C:DatabasesSQL2012ReadOnlyDB_Primary.mdf'
         ),
FILEGROUP SecondaryFG
        (Name=ReadOnlyDB_Secondary,
         FileName='C:DatabasesSQL2012ReadOnlyDB_Secondary.ndf'
        )
LOG ON (Name=ReadOnlyDB_Log,
        FileName='C:DatabasesSQL2012ReadOnlyDB_Log.ldf'
       );
GO

USE master;
GO
--Set the database to READ_ONLY
ALTER DATABASE ReadOnlyDB SET READ_ONLY;
GO

USE ReadOnlyDB;
GO
--Check the File & File-group properties
SELECT sfg.is_read_only,sfg.*
FROM sys.filegroups AS sfg;
GO
SELECT sdf.is_read_only,sdf.*
FROM sys.database_files AS sdf;
GO

image

As can be seen from the output, none of the file-groups were marked as read-only even though the database is read-only. To confirm that the database is indeed read-only, let us attempt to create a table on the database.

USE ReadOnlyDB;
GO
CREATE TABLE TestTbl (Id INT IDENTITY(1,1),
                      Name VARCHAR(50)
                     )
ON SecondaryFG;
GO

Msg 3906, Level 16, State 1, Line 1

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

A possible explanation

The fact that the file-groups are not marked read-only even though the database is read-only is counter-intuitive.

The only possible explanation that I have is that primary file-groups cannot be marked read-only which is why SQL Server does not automatically attempt to mark the file-groups and log files as read-only.

Further Reading

  • SQL Server Myth: Log files are removed when a database is made READ_ONLY [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

One thought on “#0326 – SQL Server – Setting database to READ_ONLY does not change the file-group properties

  1. Pingback: #0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s