Failed to Update Read-Only Database on Backup

A problem at work recently – the Netbackup-driven SQL backups were failing for just one database on a particular server. The database in question was, like one other, set to read-only.

I ran DBCC CHECKDB, on the off-chance that this was the problem; everything was fine.

The database was, like one other database on that server, set to READ_ONLY – and that database wasn’t having problems.

Troubleshooting

I checked in Windows Event Log, and all I got was:

BACKUP failed to complete the command BACKUP DATABASE <<name>>. Check the backup application log for detailed messages.

The Netbackup logs were of no help either.

To get an accurate error message, I ran a native SQL backup, and, in addition to the normal backup / filegroup / page messages, got the following error:

Msg 3906, Level 16, State 1, Line 35
Failed to update database "<<name>>" because the database is read-only.
Msg 3013, Level 16, State 1, Line 35
BACKUP DATABASE is terminating abnormally.

Checking the SQL Server error logs gave a clue:

[INFO] getMaxUnrecoverableCheckpointId(). Database ID: [6]. Start of Log LSN: 00000022:00000097:0023 used to trim unrecoverable checkpoint files tables during full backup.
[INFO] HkHostBackupDeleteContext(). Database ID: [6]. Cleaned up all the allocated buffers.

Hk? That sounds as though it might mean Hekaton. Checking the database properties, it was, indeed, a database with in-memory objects.

Replicating the Problem

A script to replicate the problem. Note that I’m writing to the NUL: device, a hangover from the good old days – it means the whole backup process is running, but I’m just not saving the backup files.

--First, create the database and run a backup
CREATE DATABASE ROInMemBackupTest;
GO
ALTER DATABASE ROInMemBackupTest SET READ_ONLY WITH ROLLBACK IMMEDIATE;
GO
BACKUP DATABASE RoInMemBackupTest TO DISK='NUL:';
GO
/*
Processed 344 pages for database 'RoInMemBackupTest', file 'ROInMemBackupTest' on file 1.
Processed 1 pages for database 'RoInMemBackupTest', file 'ROInMemBackupTest_log' on file 1.
BACKUP DATABASE successfully processed 345 pages in 0.064 seconds (42.091 MB/sec).
*/

--Now we add a filegroup to contain memory optimized data
ALTER DATABASE ROInMemBackupTest SET READ_WRITE;
GO
ALTER DATABASE [ROInMemBackupTest] ADD FILEGROUP [InMemData] CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE ROInMemBackupTest SET READ_ONLY WITH ROLLBACK IMMEDIATE;
GO
BACKUP DATABASE RoInMemBackupTest TO DISK='NUL:';
GO
/*
Processed 360 pages for database 'RoInMemBackupTest', file 'ROInMemBackupTest' on file 1.
Processed 3 pages for database 'RoInMemBackupTest', file 'ROInMemBackupTest_log' on file 1.
BACKUP DATABASE successfully processed 363 pages in 0.057 seconds (49.633 MB/sec).
*/

--Now we add a container to hold memory optimized data
ALTER DATABASE ROInMemBackupTest SET READ_WRITE;
GO
ALTER DATABASE ROInMemBackupTest 
ADD FILE 
(
    NAME='MemOptData', 
    FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ROInMemBackupTest_MemOptData'
)
TO FILEGROUP InMemData;
GO

--Set to RO & try another backup
ALTER DATABASE ROInMemBackupTest SET READ_ONLY WITH ROLLBACK IMMEDIATE;
GO
BACKUP DATABASE ROInMemBackupTest TO DISK='NUL:';
GO
/*
Processed 360 pages for database 'ROInMemBackupTest', file 'ROInMemBackupTest' on file 1.
Processed 0 pages for database 'ROInMemBackupTest', file 'MemOptData' on file 1.
Processed 2 pages for database 'ROInMemBackupTest', file 'ROInMemBackupTest_log' on file 1.
Msg 3906, Level 16, State 1, Line 35
Failed to update database "ROInMemBackupTest" because the database is read-only.
Msg 3013, Level 16, State 1, Line 35
BACKUP DATABASE is terminating abnormally.
*/

ALTER DATABASE ROInMemBackupTest SET READ_WRITE;
GO
BACKUP DATABASE ROInMemBackupTest TO DISK='NUL:';
GO
/*
Processed 360 pages for database 'ROInMemBackupTest', file 'ROInMemBackupTest' on file 1.
Processed 0 pages for database 'ROInMemBackupTest', file 'MemOptData' on file 1.
Processed 2 pages for database 'ROInMemBackupTest', file 'ROInMemBackupTest_log' on file 1.
BACKUP DATABASE successfully processed 362 pages in 0.069 seconds (40.895 MB/sec).
*/


--Tidy up after we're finished
DROP DATABASE ROInMemBackupTest;

Disclaimer

This has been tested on SQL2016SP2, as that’s what this server is, and also on SQLServer 2016 SP2 CU5. I’ve not yet tested it on anything else – feel free to let me know in the comments if it works / fails on other versions of SQL Server.