Last week, we discussed the concept of marking a filegroup as ReadOnly in SQL Server. This sparked some interesting questions about how backups behave and what happens when restoring a database with ReadOnly filegroups. In this blog post, we will explore these questions and shed light on the behavior of ReadOnly filegroups in SQL Server.

Creating a Database with a ReadOnly Filegroup

Let’s start by creating a database with a ReadOnly filegroup. Here is the T-SQL script to create the database:

CREATE DATABASE [ReadOnlyDB]
CONTAINMENT = NONE ON PRIMARY ( NAME = N'ReadOnlyDB' , FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB ) , FILEGROUP [ReadOnlyDB_FG] ( NAME = N'ReadOnlyDB_FG' , FILENAME = N'C:\Temp\ReadOnlyDB_FG.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'ReadOnlyDB_log' , FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10 %) 
GO

-- Mark the filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP ReadOnlyDB_FG READ_ONLY ;
GO

In the above script, we create a database called “ReadOnlyDB” with a filegroup named “ReadOnlyDB_FG”. We then mark the filegroup as read-only using the ALTER DATABASE statement.

Backup and Restore Behavior

Now, let’s explore the behavior of backups and restores when dealing with ReadOnly filegroups. We will start by taking a full backup of the database:

BACKUP DATABASE [ReadOnlyDB] TO DISK = N'C:\Temp\ReadOnlyDB.bak' WITH NOFORMAT , INIT , NAME = N'ReadOnlyDB-Full Database Backup' , SKIP , NOREWIND , NOUNLOAD , STATS = 10 , CHECKSUM
GO

Next, we will drop the database and restore it from the backup:

USE MASTER
GO

DROP DATABASE ReadOnlyDB
GO

USE [master]
RESTORE DATABASE [ReadOnlyDB] FROM DISK = N'C:\Temp\ReadOnlyDB.bak' WITH FILE = 1 , NOUNLOAD , STATS = 5
GO

After restoring the database, let’s check the filegroup’s settings for the read_only attribute:

USE ReadOnlyDB

-- Check the status
SELECT type_desc , physical_name , is_read_only FROM sys.database_files
GO

Upon checking the filegroup’s settings, we can see that our secondary filegroup is still marked as read-only. This experiment reveals an important learning point – when a database with ReadOnly filegroups is backed up, these settings are retained. Consequently, when restoring such backups, the read-only attribute is carried forward to the restored database.

We hope this experiment has provided you with valuable insights into the behavior of ReadOnly filegroups in SQL Server. If you are currently using these concepts in your environments, we would love to hear about your experiences. Please share your thoughts and feedback in the comments below.