SQL Server完全备份作业正在跳过数据库 [英] SQL server Full backup job is skipping databases

问题描述

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

I have a monthly job running on my SQL server.

• 它将所有在线数据库备份到位置 ＼ pBackupserver\e $ \MonthEndBackups\。

• It backups all the online databases to a location "\Backupserver\e$\MonthEndBackups\".

Job如下运行USP：exec [usp_dba_BackupDatabases] 3
（< a href = http://screencast.com/t/l7IS5TZK rel = nofollow> http://screencast.com/t/l7IS5TZK ）

Job runs the USP below like this: exec [usp_dba_BackupDatabases] 3 (http://screencast.com/t/l7IS5TZK)

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

It runs on the last day of the month.

我们的数据库分散在多个服务器上

Our databases were scattered around multiple servers and this job runs on those servers and this used to work.

我们将所有数据库合并到同一服务器（相同的SQL实例）上，而该作业似乎没有备份所有数据库。我不确定为什么吗？我的工作有关于失败的通知（电子邮件），并且还写入日志文件。

We consolidated all the database on the same server (same SQL instance) and this job does not seem to backup all the databases. I am not sure why? My job has notification(email) on failure and also writes to a log file.

http://screencast.com/t/8ioTZdqEMg9x
http：/ /screencast.com/t/VI3d4GLBTGoX

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

But nothing fails, so nothing is on the logs and no email notifications show up.

我知道它没有用，因为我没有在文件夹中看到完整的备份。

I know it did not work as I don't see the full backups in the folder.

这是日程表设置：

http://screencast.com/t/waeGwLSa

怎么了？我在未备份的数据库中看不到任何模式。有较大的数据库正在备份。

What could be going wrong? I don't see any pattern in the databases that are not backing up. There are larger databases that are getting backed up.

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

Can anyone think of why this could be happening? is there any way to trouble shoot this?

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 = '\\Backupserver\d$\' + @@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 = '\\Backupserver\d$\' + @@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 = '\\Backupserver\e$\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

推荐答案

尝试以下操作选项（修复可能破坏当前循环的选项，并且也会破坏 sp_MSforeachdb ，正如我所记录的此处和此处，并此处已更正）：

Try with the following options (fixing the options that are likely breaking your current loop and also break sp_MSforeachdb, as I've documented here and here, and corrected here):

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
-----------------^^^^^^^^^^^^^^^^^^

...

WHILE @@FETCH_STATUS <> -1
---------------------^^^^^

也请，请停止声明 VARCHAR 没有长度。

这篇关于SQL Server完全备份作业正在跳过数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！