May 18, 2009 at 8:39 am
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.
May 18, 2009 at 9:00 am
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).
May 18, 2009 at 9:06 am
I agree with Steve. Also, I'd run separate backup processes for each database.
May 18, 2009 at 10:03 am
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.
May 18, 2009 at 10:14 am
Change the EXEC() to SELECT, get the statements, check and see if they execute.
May 18, 2009 at 11:33 am
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.
June 3, 2009 at 3:57 am
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.
April 29, 2013 at 6:19 am
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---
April 29, 2013 at 6:36 am
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
April 29, 2013 at 10:55 am
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