Cursoring over sys.databases skips databases

I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:

I have the following code to perform database backups.

DECLARE @Filename   VARCHAR(256)
DECLARE @FileDate   VARCHAR(15) 
DECLARE @Path       VARCHAR(50) 
DECLARE @Name       VARCHAR(50) 

-- specify database backup directory
SET @Path = '\MyPath'

-- specify filename date
SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','')

DECLARE db_cursor CURSOR FOR
    SELECT [name]
        FROM master.sys.databases
        WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
            AND [state_desc] = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak'
    BACKUP DATABASE @Name
    TO DISK = @Filename
    WITH CHECKSUM,
        COMPRESSION

    FETCH NEXT FROM db_cursor INTO @Name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be.

I am trying to understand why this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query

SELECT [name]
    FROM master.sys.databases
    WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
        AND [state_desc] = 'ONLINE'

are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)

sys.databases is a complex view. On SQL Server 2016 it is:

```
CREATE VIEW sys.databases AS
SELECT d.name,
d.id AS database_id,
r.indepid AS source_database_id,
d.sid AS owner_sid,
d.crdate AS create_date,
d.cmptlevel AS compatibility_level,
-- coll.value = null means that a collation was not specified for the DB and the server default is used instead
convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation')
else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS collation_name,
iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), cast (3 as tinyint), p.user_access) AS user_access,
iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), 'NO_ACCESS', ua.name) AS user_access_desc,
sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then cast (1 as tinyint) -- RESTORING
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then cast (7 as tinyint) -- COPYING
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then cast (4 as tinyint) -- SUSPECT
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then cast (8 as tinyint) -- QUORUM_RECOVERY_PENDING
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then cast (9 as tinyint) -- CREATING
else p.state
end AS state, -- 7 is COPYING and 4 is SUSPECT state for database copy (UNDO: Need to have a clean way to set states in dbtable for a user db)
case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then 'RESTORING'
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then 'COPYING'
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then 'SUSPECT'
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then CONVERT(nvarchar(60), N'QUORUM_RECOVERY_PENDING')
when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then 'CREATING'
else st.name
end AS state_desc,
sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
case when serverproperty('EngineEdition') = 5 then convert(bit, 0) else p.is_cleanly_shutdown end AS is_cleanly_shutdown,
sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
case when (serverproperty('EngineEdition') = 5) then sysconv(tinyint, sysconv(bit,(d.status & 0x00100000)))
else p.snapshot_isolation_state end AS snapshot_isolation_state,
case when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 1) then 'ON'
when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 0) then 'OFF'
else si.name end AS snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
case when (serverproperty('EngineEdition') = 5)
then case
when sysconv(bit,(d.status & 0x00000008)) = 1
then cast(3 as tinyint)
when sysconv(bit,(d.status & 0x00000004)) = 1
then cast(2 as tinyint)
else
cast(1 as tinyint)
end
else p.recovery_model
end AS recovery_model,
case when (serverproperty('EngineEdition') = 5)
then case
when sysconv(bit,(d.status & 0x00000008)) = 1
then CONVERT(nvarchar(60), N'SIMPLE')
when sysconv(bit,(d.status & 0x00000004)) = 1
then CONVERT(nvarchar(60), N'BULK_LOGGED')
else
CONVERT(nvarchar(60), N'FULL')
end
else ro.name
end AS recovery_model_desc,
p.page_verify_option, pv.name AS page_verify_option_desc,
sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
sysconv(bit, d.status2 & 0x00400000) AS is_auto_create_stats_incremental_on, -- DBR_AUTOCRTSTATSINC
sysconv(bit, d.status2 & 0x4

StackExchange Database Administrators Q#261540, answer score: 8