Differential backup failure

  • Hi Team,

    I am facing an issue with differential back up . The differential backup is failed only on sunday . we are taking diff backup every 12 hours .only on sunday the diff backup is failed only once .

    Except on sunday we are not seeing any issues .........

    Thanks ,

    lavanya

  • And the error message the backup gives is....?

    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 ,

    Error 3041 severity 16 state 1

    BACKUP failed to complete the command BACKUP DATABASE database name WITH DIFFERENTIAL. Check the backup application log for detailed messages.

    Thanks,

    Lavanya

  • Lavanyasri (10/2/2012)


    Check the backup application log for detailed messages.

    And?

    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
  • Executed as user: NT AUTHORITY\SYSTEM. ...ercent processed. [SQLSTATE 01000] (Message 3211) 97 percent processed. [SQLSTATE 01000] (Message 3211) Processed 40 pages for database 'BACKUP_xn', file 'BACKUP__xn_Data' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'BACKUP__xN', file 'BACKUP__xn_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.110 seconds (3.048 MB/sec). [SQLSTATE 01000] (Message 3014) 58 percent processed. [SQLSTATE 01000] (Message 3211) 78 percent processed. [SQLSTATE 01000] (Message 3211) 97 percent processed. [SQLSTATE 01000] (Message 3211) Processed 40 pages for database 'BACKUP_xn', file 'BACKUP_xn_Data' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'BACKUP_xN', file 'BACKUP_xR... The step failed.

  • Is this being done as part of a maintenance plan? My guess is that something other than the backup itself is failing - possibly the removal of an old backup file. Is your maintenance plan configured to delete files older than a certain age?

    Two things I'd check, therefore:

    (1) Run RESTORE VERIFYONLY FROM DISK = '<Path to backup file>'

    (2) Does the backup file location contain files that are older than they should be?

    Might also be worth doing a test restore from a full backup plus differential to give you full peace of mind.

    John

  • If you are using a maintenance plan, look closely at the t-sql statement(s) it created. The maintenance plan interface does not always display all of the databases being backed up in the job.

  • In job step advanced properties, set the output file, and try again. Then use the output file to determine what's going on; the job step history is limited to a near-worthlessly small amount of data for cases like this (as you can see when it cuts off the detailed information and gives the 'the step failed' generic message.

  • Maybe that differential backup on sunday starts before the full backup is finished. I guess you take full backups once a week. Otherwise the differentials will grow steadily and finally become as large as a full backup. The database will stay available for most operations during a backup. But you cannot start any type of backup during any (other or same) type of backup. Which is for obvious reasons if you think about it for a moment ...

  • vliet (10/2/2012)


    But you cannot start any type of backup during any (other or same) type of backup.

    That's not entirely true. You can run a log backup while the full backup is in progress. You may be right about differentials, though - I don't think I've ever tried it.

    John

  • the remaining diff backups on sunday went thru fine so i guess this is a one off , most probably something interefered with the backup operation. like mentioned ealrier test your backups once more and verify the order and actual operations being performed on sunday at that time.

    Also monitor the backups for another week to confirm the issue is a one off else you might be looking at another job or weekend process thats causing the problem and needs to be rescheduled.

    Jayanth Kurup[/url]

  • Hi All,

    Thanks for your valuable infrmation.

    we are not using maintenance plan . We created a job with backup database script for all user databases to run every 12 hours . Only on sunday the diff backup is failing .. the retention period is 2 days . today i am unable to see the sunday diff backup files ..

    Thanks ,

    Lavanya

  • Lavanya

    Please will you post the script, then?

    By the way, you can see what differential backups have completed with this query:

    SELECT

    r.DBName

    ,r.type

    ,r.BackupStart

    ,s.backup_finish_date

    ,s.backup_size

    ,m.physical_device_name

    FROM (SELECT

    d.name AS DBName

    ,b.type

    ,b.backup_start_date AS BackupStart

    FROM master.dbo.sysdatabases d

    LEFT JOIN msdb.dbo.backupset b

    ON d.name = b.database_name

    JOIN msdb..backupmediafamily f

    ON b.media_set_id = f.media_set_id) r

    JOIN msdb.dbo.backupset s

    ON r.DBName = s.database_name

    AND r.type = s.type

    AND r.BackupStart = s.backup_start_date

    JOIN msdb.dbo.backupmediafamily m

    ON s.media_set_id = m.media_set_id

    WHERE r.type = 'I'

    ORDER BY r.DBName, r.type

    John

  • vliet (10/2/2012)


    But you cannot start any type of backup during any (other or same) type of backup. Which is for obvious reasons if you think about it for a moment ...

    You can. One will block the other if you're running full or diff, but they won't give errors. Log backups can run simultaneously with full or diff backups.

    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 Gila,

    Thans for the update .

    This weekend also we had the same problem .

    By using the script we are taking the backups,no maintenance plans

    The diff backup job is failed on Sunday morning . We have 50 databases in that first 4 databases diff backup is success in the morning .

    Those files are available in the physical location but the 4 th database history was not available in the backup set ,(select * from backupset where databasename =’xxxxxxx’).

    I checked the 4th database backup file with restore verify only ,out put shows the backup set on file1 is valid.

    Thanks

    Lavanya

Viewing 15 posts - 1 through 15 (of 17 total)

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