SQL 服务器完整备份作业正在跳过数据库

Question

我的SQL服务器上有一个每月运行的工作。

• 它将所有联机数据库备份到位置"\Backupserver\e$\MonthEndBackups\"。

• Job 像这样运行下面的 USP：exec [usp_dba_BackupDatabases] 3(http://screencast.com/t/l7IS5TZK(

• 它在每月的最后一天运行。

我们的数据库分散在多台服务器上，此作业在这些服务器上运行，这曾经有效。

我们在同一台服务器(同一 SQL 实例(上合并了所有数据库，并且此作业似乎没有备份所有数据库。我不知道为什么？我的作业在失败时有通知(电子邮件(，并写入日志文件。

http://screencast.com/t/8ioTZdqEMg9xhttp://screencast.com/t/VI3d4GLBTGoX

但是没有任何失败，因此日志上没有任何内容，也没有电子邮件通知显示。

我知道它不起作用，因为我在文件夹中看不到完整备份。

以下是计划设置：

http://screencast.com/t/waeGwLSa

可能出现什么问题？我在数据库中没有看到任何未备份的模式。有更大的数据库正在备份。

谁能想到为什么会发生这种情况？ 有没有办法解决这个问题？

USE [DBA]
GO
/****** Object:  StoredProcedure [dbo].[usp_dba_BackupDatabases]    Script Date: 10/01/2013 11:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_dba_BackupDatabases] 
    @pBackupType        SMALLINT
    ,@pDatabaseName     sysname = NULL
AS

SET NOCOUNT ON

DECLARE @vDatabase sysname, @sql VARCHAR(MAX), @vBackupFileFullPath VARCHAR(MAX)
DECLARE c CURSOR FOR 
SELECT  name FROM sys.sysdatabases
WHERE name NOT IN('tempdb', 'model')
    AND DATABASEPROPERTYEX (name,'STATUS') IN( 'ONLINE')
    AND (name  = @pDatabaseName OR @pDatabaseName IS NULL)
OPEN c
FETCH NEXT FROM c INTO @vDatabase
WHILE @@FETCH_STATUS = 0 
BEGIN
SELECT CONVERT(VARCHAR,GETDATE(),121)
    PRINT '|-->' + CONVERT(VARCHAR,GETDATE(),121) +' Backup Start for database ' + @vDatabase 
    IF @pBackupType = 1
        BEGIN
            SET @vBackupFileFullPath = '\Backupserverd$' + @@SERVERNAME +'' + @vDatabase +'_DB_'
                +REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),GETDATE(),112),'-',''),' ',''),':','') +'.bak'
            SET @sql  = 'BACKUP DATABASE ' + @vDatabase + ' TO  DISK = N' + CHAR(39) + @vBackupFileFullPath +  char(39) 
            + ' WITH FORMAT, INIT,  NAME = N' + CHAR(39) + @vDatabase + ' -Full Database Backup' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD,  STATS = 5, CHECKSUM'
        END

    IF @pBackupType = 2
        BEGIN
            SET @vBackupFileFullPath = '\Backupserverd$' + @@SERVERNAME + 'Differential' + @vDatabase +'_Diff_'
                 +REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),GETDATE(),121),'-',''),' ',''),':','') +'.bak'        
            SET @sql  = 'BACKUP DATABASE ' + @vDatabase + ' TO  DISK = N' + CHAR(39) +  @vBackupFileFullPath + char(39)
                 + ' WITH DIFFERENTIAL , FORMAT, INIT, NAME = N' + CHAR(39) + @vDatabase 
                                + ' -Differential Database Backup' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD,  STATS = 5, CHECKSUM'              
        END
    IF @pBackupType = 3
        BEGIN
            SET @vBackupFileFullPath = '\Backupservere$MonthEndBackups' + @@SERVERNAME +'_'+ @vDatabase +'_db.bak'
            SET @sql  = 'BACKUP DATABASE ' + @vDatabase + ' TO  DISK = N' + CHAR(39) + @vBackupFileFullPath  + char(39) 
                + ' WITH COPY_ONLY, FORMAT, INIT,  NAME = N' + CHAR(39) + @vDatabase + ' -Full Month End Database Backup' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD,  STATS = 5, CHECKSUM'           
        END
    PRINT ' |'+ @sql  
    EXEC (@sql )

    --VERIFY BACKUP
    IF @pBackupType = 1
        SET @sql  = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39) + @vBackupFileFullPath +  char(39) 
                    + ' WITH  FILE = 1,  NOUNLOAD,  NOREWIND'
    IF @pBackupType = 2
        SET @sql  = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39) + @vBackupFileFullPath + char(39)
                 + ' WITH  FILE = 1,  NOUNLOAD,  NOREWIND'
    IF @pBackupType = 3
        SET @sql  = 'RESTORE VERIFYONLY FROM DISK = N' + CHAR(39) + @vBackupFileFullPath + char(39) 
                    + ' WITH  FILE = 1,  NOUNLOAD,  NOREWIND'
    --PRINT '   |'+ @sql  
    --EXEC (@sql )

    PRINT '|-->' + CONVERT(VARCHAR,GETDATE(),121) +' Backup Complete for database ' + @vDatabase 
    PRINT '' 
    FETCH NEXT FROM c INTO @vDatabase
END
CLOSE c
DEALLOCATE c

GO

Answer 1

尝试使用以下选项(修复可能破坏当前循环并中断sp_MSforeachdb的选项，正如我在此处记录并在此处更正的那样(：

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
-----------------^^^^^^^^^^^^^^^^^^
...
WHILE @@FETCH_STATUS <> -1
---------------------^^^^^

另外，请停止声明没有长度的VARCHAR。