SQL BACKUP JOB Failure

  • I am currently trying to investigate why our nightly full backups have been failing. The nightly full backups were working correctly but have now stopped. I have had a look in application logs and get these following messages.

    Executed as user: NT AUTHORITY\SYSTEM. ...ercent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (Message 3211) 41 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent processed. [SQLSTATE 01000] (Message 3211) 61 percent processed. [SQLSTATE 01000] (Message 3211) 70 percent processed. [SQLSTATE 01000] (Message 3211) 81 percent processed. [SQLSTATE 01000] (Message 3211) 90 percent processed. [SQLSTATE 01000] (Message 3211) Processed 360 pages for database 'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 2 pages for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 362 pages in 5.107 seconds (0.580 MB/sec). [SQLSTATE 01000] (Message 3014) 10 percent processed. [SQLSTATE 01000] (Message 3211) 20 percent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (M... The step failed.

    The backup data at the end of "nwcscmdsdataBACK(\\brentsan\e$\Brentvmsollis-BackupwcscmdsdataBACK.BAK)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

    Error: 3266, Severity: 16, State: 1.

    BACKUP failed to complete the command BACKUP DATABASE NWCSCMDSDataREP. Check the backup application log for detailed messages.

    Error: 3041, Severity: 16, State: 1.

    I am not sure if this a permissions issue or disk size issue. I think it is unlikely to be a permissions issue as the Log backups are working fine. Any help would help as to what might be the cause would be much appreciated.

  • A couple things.

    First, can you post the backup command?

    Second, does this run interactively? If you run from SSMS?

    Last, backups to remove drives (shares) are problematic, and flaky. The backup process throws pages out as fast as it can. If there is a network delay, it fails the backup. If a copy has a network error, it retries.

    You would do better to back up locally and copy to the network (and delete locally).

  • I agree with Steve. Also, I'd run separate backup processes for each database.

  • The BACKUP Command is shown below

    DECLARE @securedb varchar(255)

    IF EXISTS (SELECT * FROM NWCSCMDSData.dbo.sysobjects WHERE id = object_id(N'NWCSCMDSData.[dbo].[GPSecureDatabase]') AND xtype = 'U')

    BEGIN

    SET @securedb = (SELECT TOP 1 GPSecureDB FROM NWCSCMDSData..GPSecureDatabase)

    END

    declare dblist cursor for select name from master..sysdatabases

    where not name in ('tempdb','model')

    open dblist

    declare @dn varchar(100)

    fetch next from dblist into @dn

    WHILE (@@FETCH_STATUS -1)

    begin

    exec ('BACKUP DATABASE [' + @dn + '] to [' + @dn + 'BACK] WITH NAME=''' + @dn +' Backup'' , INIT,STATS=10')

    --exec ('sp_dboption ''' + @dn + ''',''autoclose'',''false''')

    fetch next from dblist into @dn

    end

    close dblist

    deallocate dblist

    end

    It is run as a sql job nightly monday to friday.

  • Change the EXEC() to SELECT, get the statements, check and see if they execute.

  • I have changed the exec statements to select and they execute.

    Incidentally the SQL-Server instance sits on a virtual server. I am wondering if that might have any impact. We were also recently attacked by a conficker virus. However this has not impacted on the log backup or other maintenance plans that we run.

  • I have managed to get the backup to work. I deleted two of the backups in the folder and nightly schedule ran smooth. Two of the backups had become corrupt and the backup just seemed to keel over when it came to backing them up.

    thanks for your input.

  • Hi ,

    let me know you are taking backup to local disk or network path . i guess you are taking backup to network path .

    possible reason :

    the backup failed due to network issue or in b\w the network was disconnected .

    possible soluation:

    improve the network performance b\w your local system and network path and some times

    reran the job also will solve the problem.

    --subahan munthamadugu---

  • Please note: 4 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi ,

    let me know you are taking backup to local disk or network path . i guess you are taking backup to network path and backup sizes are also big .

    possible reason :

    the backup failed due to network issue or in b\w the network was disconnected .

    possible soluation:

    1. improve the network performance b\w your local system and network path and some times

    reran the job also will solve the problem.

    2.suppose your databases are very big then stripe and take backup to net work path.

    3. put retry option on your backup step.

    --subahan munthamadugu---

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply