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;

view plainprint?

1. USE master;  
2. GO  
3. -- creating the database  
4. CREATE DATABASE [TestDatabase]  
5.  CONTAINMENT = NONE  
6.  ON  PRIMARY   
7. ( NAME = N'TestDatabase_Data_1', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data1.mdf'   
8.  , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
9.   
10.  FILEGROUP [FileGroup_Main]   
11. ( NAME = N'TestDatabase_Dat_2', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data2.ndf'   
12.  , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  
13.    
14.  FILEGROUP [FileGroup_History]   
15. ( NAME = N'TestDatabase_Dat_3', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Data3.ndf'   
16.  , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
17.   
18.  LOG ON   
19. ( NAME = N'TestDatabase_Log', FILENAME = N'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDatabase_Log.ldf'   
20.  , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
21. GO  
22.   
23. -- creating tables and inserting records  
24. USE [TestDatabase]  
25. GO  
26.   
27. CREATE TABLE [dbo].[Configuration]  
28. (  
29.  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,  
30.  [ConfigurationName] [varchar](100) NOT NULL,  
31.  [ConfigurationValue] [varchar](100) NOT NULL,  
32.  [Settings] char(4000) NOT NULL  
33. ) ON [PRIMARY]  
34.   
35. GO  
36.   
37. CREATE TABLE [dbo].[Sales]  
38. (  
39.  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,  
40.  [SalesDate] [date] NOT NULL,  
41.  [Amount] [decimal](16, 2) NOT NULL,  
42.  [Settings] char(4000) NOT NULL  
43. ) ON [FileGroup_Main]  
44. GO  
45.   
46. CREATE TABLE [dbo].[Sales_History]  
47. (  
48.  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,  
49.  [SalesDate] [date] NOT NULL,  
50.  [Amount] [decimal](16, 2) NOT NULL,  
51.  [Settings] char(4000) NOT NULL  
52. ) ON [FileGroup_History]  
53. GO  
54.   
55. -- Insert set of records  
56. INSERT INTO dbo.Configuration (ConfigurationName, ConfigurationValue, Settings) VALUES ('A', '1', 'a');  
57. GO 5000  
58. INSERT INTO dbo.Sales (SalesDate, Amount, Settings) VALUES ('01/01/2016', 10000, 'a');  
59. GO 5000  
60. INSERT INTO dbo.Sales_History (SalesDate, Amount, Settings) VALUES ('01/01/2010', 10000, 'a');  
61. GO 5000  
62.   
63.   
64. USE master;  
65. GO  
66.   
67. -- Make one FG as read-only  
68. ALTER DATABASE TestDatabase MODIFY FILEGROUP [FileGroup_History] READ_ONLY;  
69. GO  
70.   
71. -- take a full database backup  
72. -- this takes all three files  
73. -- backup size is 63 MB  
74. BACKUP DATABASE TestDatabase   
75. TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup01.bak'  
76. WITH INIT  
77. GO  
78.   
79. -- take a backup with READ_WRITE_FILEGROUPS  
80. -- this takes only TestDatabase_Data_1 and TestDatabase_Dat_2  
81. -- backup size is 43 MB  
82. BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS  
83. TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup02.bak'  
84. WITH INIT  
85. GO  
86.   
87. -- Make the database as read-only  
88. ALTER DATABASE [TestDatabase] SET  READ_ONLY   
89. GO  
90.   
91. -- take a backup with READ_WRITE_FILEGROUPS  
92. -- as per MSDN, this should take only PRIMARY  
93. -- But this takes all three files with RTM and exlude read-only files with CU2  
94. -- backup size is 63 MB  
95. BACKUP DATABASE TestDatabase READ_WRITE_FILEGROUPS  
96. TO DISK = 'D:\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\TestDatabaseBackup03.bak'  
97. WITH INIT  
98. GO  

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.