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.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , . Bookmark the permalink.

3 Responses to Failed to Update Read-Only Database on Backup

  1. Sir it means we can keep the hekaton database in read-only but we can’t take it’s backup in this mode.
    Please correct me

    • thomasrushton says:

      Correct.

      And it’s the same on SQL2017. One of my contacts at Microsoft is running this into their support system.

  2. thomasrushton says:

    This was a few months back. I did hear back that this is by design, as the codebase is shared with that for filestream data. Yeah, I’m not sure how the logic works there, and I’ve left that particular gig so can’t follow up on their support contract. :-/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.