SQL Server 2016 Partial backup operation on a READONLY database backs up all file groups

While I was writing a code on backing up operation for SQL Server 2016, I noticed that there is an issue with partial backup. As per documentations, when we take a partial backup using READ_WRITE_FILEGROUPS option, it backs up;

• only PRIMARY, all READ/WRITE file groups and mentioned READONLY file groups.
• only PRIMARY if the database is READONLY.

Read more on this at: https://msdn.microsoft.com/en-us/library/ms191539.aspx

However, if you perform this operation with SQL Server 2016 RTM, it backs up;

• all file groups; PRIMARY, all READ/WRITE file groups including READONLY file groups - which is NOT the expected behavior.

You can apply the latest cumulative update (I applied CU2), then it backs up;

• only PRIMARY and all READ/WRITE file groups - which is still NOT the expected behavior.

Here is the code I tested, you also can run the same and see;

USE master;
GO
-- creating the database
CREATE DATABASE [TestDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDatabase_Data_1', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data1.mdf' 
 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 

 FILEGROUP [FileGroup_Main] 
( NAME = N'TestDatabase_Dat_2', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data2.ndf' 
 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 
 FILEGROUP [FileGroup_History] 
( NAME = N'TestDatabase_Dat_3', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data3.ndf' 
 , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 
( NAME = N'TestDatabase_Log', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Log.ldf' 
 , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- creating tables and inserting records
USE [TestDatabase]
GO

CREATE TABLE [dbo].[Configuration]
(
 [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [ConfigurationName] [varchar](100) NOT NULL,
 [ConfigurationValue] [varchar](100) NOT NULL,
 [Settings] char(4000) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Sales]
(
 [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [SalesDate] [date] NOT NULL,
 [Amount] [decimal](16, 2) NOT NULL,
 [Settings] char(4000) NOT NULL
) ON [FileGroup_Main]
GO

CREATE TABLE [dbo].[Sales_History]
(
 [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [SalesDate] [date] NOT NULL,
 [Amount] [decimal](16, 2) NOT NULL,
 [Settings] char(4000) NOT NULL
) ON [FileGroup_History]
GO

-- Insert set of records
INSERT INTO dbo.Configuration (ConfigurationName, ConfigurationValue, Settings) VALUES ('A', '1', 'a');
GO 5000
INSERT INTO dbo.Sales (SalesDate, Amount, Settings) VALUES ('01/01/2016', 10000, 'a');
GO 5000
INSERT INTO dbo.Sales_History (SalesDate, Amount, Settings) VALUES ('01/01/2010', 10000, 'a');
GO 5000


USE master;
GO

-- Make one FG as read-only
ALTER DATABASE TestDatabase MODIFY FILEGROUP [FileGroup_History] READ_ONLY;
GO

-- take a full database backup
-- this takes all three files
-- backup size is 63 MB
BACKUP DATABASE TestDatabase 
TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup01.bak'
WITH INIT
GO

-- take a backup with READ_WRITE_FILEGROUPS
-- this takes only TestDatabase_Data_1 and TestDatabase_Dat_2
-- backup size is 43 MB
BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS
TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup02.bak'
WITH INIT
GO

-- Make the database as read-only
ALTER DATABASE [TestDatabase] SET  READ_ONLY 
GO

-- take a backup with READ_WRITE_FILEGROUPS
-- as per MSDN, this should take only PRIMARY
-- But this takes all three files with RTM and exlude read-only files with CU2
-- backup size is 63 MB
BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS
TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup03.bak'
WITH INIT
GO

Result before applying CU2;

Processed 2840 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_3' on file 1.

Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.

BACKUP DATABASE successfully processed 7898 pages in 0.275 seconds (224.357 MB/sec).

Processed 2840 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.

Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.

BACKUP DATABASE...FILE= successfully processed 5370 pages in 0.191 seconds (219.611 MB/sec).

Processed 2840 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.

Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_3' on file 1.

Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.

BACKUP DATABASE...FILE= successfully processed 7898 pages in 0.271 seconds (227.674 MB/sec).

Result after applying CU2.

Processed 2872 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_3' on file 1.
Processed 3 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.
BACKUP DATABASE successfully processed 7931 pages in 0.289 seconds (214.379 MB/sec).
Processed 2872 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.
Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.
BACKUP DATABASE...FILE= successfully processed 5402 pages in 0.202 seconds (208.890 MB/sec).
Processed 2872 pages for database 'TestDatabase', file 'TestDatabase_Data_1' on file 1.
Processed 2528 pages for database 'TestDatabase', file 'TestDatabase_Dat_2' on file 1.
Processed 2 pages for database 'TestDatabase', file 'TestDatabase_Log' on file 1.
BACKUP DATABASE...FILE= successfully processed 5402 pages in 0.205 seconds (205.852 MB/sec).

Be aware on this, this may be the expected behavior of SQL Server 2016 though I see it as an issue, working on it, I will update the post if I hear something new on it.