SQL SERVER – Backup and Restore Behavior of ReadOnly Filegroup Databases

2 Comments

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.

SQL SERVER - Backup and Restore Behavior of ReadOnly Filegroup Databases readonlydb-backup-01

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)

SQL Backup and Restore

Related Posts

2 Comments. Leave new

  • Still no luck, DR database is in standby/readonly mode upto threshold limit 45 mins and again after it has gone to restoring mode. In the error logs i am getting the same error.

    Log shipping restore job giving below error again and again….

    ” *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
    *** Error: ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed.(System.Data) *** ”

    copy job is taking the backup from the primary shared folder, but restore job not restoring properly… Please suggest me

    Reply
  • Hi Pinal, Is it possible to take the backup of readonly filegroups without taking the full database backup and then restore them back when needed?
    The question is related to this scenario – we have a huge database where partitioning is done for tables on the basis of datekey column in the table so that the each month’s data is stored in a new filegroup for all fact tables. Dimension tables are stored on primary file group. To reduce the storage size we want to make old file groups readonly and then want to take back up and if needed later we would like to restore them back. But at this point of time we are not sure if this can be done

    Reply

Leave a Reply