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)
Asked by SE1986
(2182 rep)
Mar 9, 2020, 05:06 PM
Last activity: Aug 5, 2025, 05:12 PM
Last activity: Aug 5, 2025, 05:12 PM