October 1, 2012 at 12:46 am
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
October 1, 2012 at 1:13 am
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
October 2, 2012 at 6:09 am
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
October 2, 2012 at 6:16 am
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
October 2, 2012 at 6:36 am
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.
October 2, 2012 at 6:46 am
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
October 2, 2012 at 7:04 am
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.
October 2, 2012 at 7:45 am
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.
October 2, 2012 at 7:54 am
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 ...
October 2, 2012 at 8:00 am
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
October 2, 2012 at 8:04 am
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.
October 2, 2012 at 8:34 am
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
October 2, 2012 at 8:42 am
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
October 2, 2012 at 9:37 am
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
October 8, 2012 at 1:57 am
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