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:
- Create a test database with multiple file-groups (the test works equally well with a single file-group)
- Set the database to READ_ONLY
- 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
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.
- SQL Server Myth: Log files are removed when a database is made READ_ONLY [Link]
Until we meet next time,