SQL SERVER – Backup and Restore Behavior of ReadOnly Filegroup Databases

Last week I wrote about SQL SERVER – Marking Filegroup as ReadOnly with SQL Server and it got me interested into few key questions. One of the questions someone asked was, how will backups behave? Are there anything I need to know about ReadOnly Filegroups? Will these databases when restored take this setting along? So in this blog post, let me take few simple steps in the learning journey I had when working with ReadOnly filegroups.

TSQL Scripts

Let us start out by creating the database first.
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

I have gone ahead by marking the filegroup as Read_Only. Next I am going ahead with a FULL Backup.

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 the same.

-- Clean up time
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


In the above command, we restored the database back to the same location. Now let us go ahead and check the filegroup’s settings for read_only attribute.

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


You can see our secondary filegroup is still marked as read_only.

There is one important learning that I got in this experiment. The database fileroups that are marked as read_only is retained as part of fullbackup and when we restore such backups, these settings get carried forward in our restored database.

I hope you got some learning as part of this experiment. Are you using these concepts in your environments? Do let us know via the comments below.

Reference: Pinal Dave (https://blog.sqlauthority.com)