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://darkslategrey-bat-805937.hostingersite.com)
