Is the error that I am using a cursor or is there an issue with the rest of this script?

This code works fine in SQL 2005 but appears to miss a random number of records from the end of the select in SQL 2008 or SQL 2008R2. I use this code to backup databases on my production servers. The 2008 Server has 37 db's on it (not counting tempdb) and it backs up between 17 to 35 of those db's each day (even though if I run the select I always get 37 rows returned). The job it is in completes with no errors, but doesn't back up all of the databases.

DECLARE @today VARCHAR(10)
SELECT @today = Convert(varchar(10),dateadd(day,0,Dateadd(day,datediff(day,0,getdate()),0)),120)

DECLARE @DBName varchar(500)
DECLARE DB_Cursor CURSOR FOR
SELECT name FROM sys.databases 
OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
   BEGIN
IF @DBNAME <> 'tempdb'
BEGIN
    declare @Path varchar(500)              
    select @Path = 'g:\DBBackups\'

    declare @FileName varchar(4000)
    select @FileName = @Path + @DBNAME + '_Full_' + @today + '.bak'

        BACKUP DATABASE @DBName 
            TO DISK = @FileName
            WITH NoInit, NoFormat, SKIP
END

  FETCH NEXT FROM DB_Cursor INTO @DBNAME;
END;
CLOSE DB_Cursor;
DEA开发者_运维技巧LLOCATE DB_Cursor;

I talked about this briefly starting here:

• Execute a Command in the Context of Each Database in SQL Server - Part 2

Basically, change the cursor to use READ_ONLY LOCAL FORWARD_ONLY STATIC and you shouldn't be affected by locks or changes to databases (which is the only wild stab guess I have for an actual explanation). This was the only true difference I could find in all the cases where the mysterious database skipping did not occur.

I did not attempt to investigate the values of @@FETCH_STATUS - it is possible that as the cursor progresses, this changes to values other than 0 and -1 (I usually check against the latter, not the former). So maybe change WHILE @@FETCH_STATUS = 0 to WHILE @@FETCH_STATUS <> -1.

Or better yet, don't use a cursor at all. :)

The script is heavily flawed. It doesn't check the state of the database so if you have a database that is not online like a database mirror or log shipping secondary, the process will fail and all databases that come after it won't be backed up. Also, your using the wrong data types