Backup Job Sometimes Stops at msdb

Hi,

We have been using the same stored procedures for backing up our

databases for years with no issues. We now have a puzzling issue on SQL

Server 2005 (sp1) and msdb. The stored procedure uses a cursor to get a

list of databases in the group (alphabetically). The cursor always gets

all of the databases but sometimes it doesn't back all of them up (like

it thinks that it's done). It always seems to stop at msdb. It's not

because msdb is the last in the list, I created a database called mz

and it still only backed up master and model (the only other two in

this group). I used @.@.cursor_rows so I know that all for database names

were in the cursor. This doesn't ALWAYS happen - sometimes it backs up

all of the databases just fine. This seems to ONLY happen on the 64-bit

machines. We're using litespeed and I'll try to reproduce the issue

using native commands.

DECLARE @.db VARCHAR(100) -- database name

DECLARE @.NumBakFiles INT -- number of backup files to be created

DECLARE @.DestServer VARCHAR(50) -- backup destination server

DECLARE @.DestShare VARCHAR(50) -- backup destination file share

DECLARE @.dbDestDisk VARCHAR(300) -- database backup destination disk
DECLARE cDB CURSOR FOR

SELECT [name]

FROM master.sys.databases

--WHERE [name] in ('msdb')

WHERE UPPER([name]) NOT IN('TEMPDB', 'NORTHWIND', 'PUBS','SQLPROFILE')

-- AND UPPER([name]) < 'H' -- AthruG

AND [name] > 'h%' -- HthruM

AND [name] < 'n%' -- HthruM

-- AND [name] >= 'n%' -- NthruS

-- AND [name] <= 't%' -- NthruS

-- AND [name] <= 't%' -- TthruZ

ORDER BY

[name],

create_date

OPEN cDB

print @.@.cursor_rows

FETCH NEXT

FROM cDB

INTO @.db

WHILE (@.@.FETCH_STATUS=0)

BEGIN

PRINT @.db

SET @.NumBakFiles = 1

SET @.DestServer = '\\SQLDUMP\'

--SELECT @.DestServer = 'D:\SQLDATADUMP\'

SET @.DestShare = 'SQLDUMPSHARE\'

SET @.dbDestDisk = @.DestServer + @.DestShare +

REPLACE(@.@.SERVERNAME, '\', '_') + '\SQLDATADUMP\' + @.db + '_FullDB_' +

REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':', ''), ' ','')

+ '.bak'

--SELECT @.dbDestDisk = @.DestServer + '\' + @.db +

'_FullDB_' + REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 113), ':',

''), ' ','') + '.bak'

-- Backup Database File

EXEC master.dbo.xp_backup_database @.database = @.db, @.filename = @.dbDestDisk, @.init=1

FETCH NEXT

FROM cDB

INTO @.db

END --WHILE

CLOSE cDB

DEALLOCATE cDB

GOI was able to reproduce the issue using native sql server commands:

4

master

Processed 360 pages for database 'master', file 'master' on file 10.

Processed 2 pages for database 'master', file 'mastlog' on file 10.

BACKUP DATABASE successfully processed 362 pages in 0.186 seconds (15.921 MB/sec).

model

Processed 160 pages for database 'model', file 'modeldev' on file 10.

Processed 2 pages for database 'model', file 'modellog' on file 10.

BACKUP DATABASE successfully processed 162 pages in 0.081 seconds (16.333 MB/sec).|||I changed the cursor to a static cursor and have not had any issues

since then. It was intermittent before but it happened at least once or

twice per week.